public static string getColumnNameOrAlias(SQLBuilder.Clauses.Column Column)
{
string colNameprAlias = "";
if (Column.AliasName == System.String.Empty)
{
colNameprAlias = Column.Name;
}
else
{
colNameprAlias = Column.AliasName;
}
return(colNameprAlias);
}
public static DataTable getColumnStats(string connectionString, SelectQueryBuilder queryBuilder, SQLBuilder.Clauses.Column column)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException(connectionString);
}
int totalCols = 0;
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
if (queryBuilder.CrossTabClause.Col != null)
{
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
}
DataSet dataSet = new DataSet();
DataSet dataSetColumn = new DataSet();
if (queryBuilder.GroupByColumns.Count() == 0)
{
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
string countQuery = "select count(distinct " + SelectQueryBuilder.getColumnPartQuery(column) + ") " + queryWithoutSelect + ";";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(countQuery, connection);
dataAdapter.Fill(dataSet);
DataTable countQueryResults = dataSet.Tables[0];
foreach (DataRow row in countQueryResults.Rows)
{
Int64 totalRowCount = Convert.ToInt64(row[countQueryResults.Columns[0]]);
if (totalRowCount > 100)
{
throw new Exception("Too many vlaues...");
}
}
string selectPartQuery = queryBuilder.getSelectPartQuery(0, -1, out totalCols);
//string finalQuery = "select distinct " + SelectQueryBuilder.getColumnPartQuery(column) + " " + queryWithoutSelect + ";";
string finalQuery = "select " + SelectQueryBuilder.getColumnPartQuery(column) + ", count(*) as Count " + queryWithoutSelect
+ " group by " + SelectQueryBuilder.getColumnPartQuery(column)
+ " order by " + SelectQueryBuilder.getColumnPartQuery(column) + ";";
dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSetColumn);
}
else
{
string finalQuery = "select distinct " + SelectQueryBuilder.getColumnPartQuery(column) + " from (" + queryBuilder.getQueryforGroupBy(out totalCols) + ");";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
}
Console.WriteLine("Total time in query execution: " + (DateTime.Now - startTime));
return(dataSetColumn.Tables[0]);
}
finally
{
connection.Close();
}
}
public static CrossTabResults getCrossTabulationData(string connectionString, SelectQueryBuilder queryBuilder, out Int64 totalItems, out int totalCols)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException(connectionString);
}
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
DataSet dataSet = new DataSet();
totalItems = 0;
CrossTabResults crossTabResults = queryBuilder.CrossTabulationResults;
if (queryBuilder.GroupByColumns.Count() == 0)
{
throw new Exception("Calling wrong method!!!");
}
else
{
string finalQuery = queryBuilder.getQueryforGroupBy(out totalCols);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
totalItems = dataSet.Tables[0].Rows.Count;
}
crossTabResults.Results = dataSet.Tables[0];
List <string> columnTypes = new List <string>();
for (int colIndex = 0; colIndex < dataSet.Tables[0].Columns.Count; colIndex++)
{
columnTypes.Add(dataSet.Tables[0].Columns[colIndex].DataType.FullName);
}
DataView dataView = crossTabResults.Results.DefaultView;
Dictionary <string, Object> dataMap = new Dictionary <string, Object>();
List <List <string> > groupByColumnValueList = new List <List <string> >();
List <string> keyPrefixes = new List <string>();
List <string> groupByColumnValue = null;
List <string> prevGroupByColumnValue = null;
List <string> crossTabColumnValues = new List <string>();
string key = "";
for (int rowIndex = 0; rowIndex < dataView.Count; rowIndex++)
{
groupByColumnValue = new List <string>();
int colIndex = 0;
for (; colIndex < crossTabResults.GroupByColumns.Count; colIndex++)
{
string value = dataView[rowIndex].Row[colIndex].ToString();
groupByColumnValue.Add(value);
}
if (prevGroupByColumnValue == null || !groupByColumnValue.SequenceEqual(prevGroupByColumnValue))
{
prevGroupByColumnValue = groupByColumnValue;
groupByColumnValueList.Add(groupByColumnValue);
key = "";
for (int i = 0; i < groupByColumnValue.Count; i++)
{
key += groupByColumnValue.ElementAt <string>(i);
}
key += "~";
keyPrefixes.Add(key);
}
string crossTabColValue = dataView[rowIndex].Row[colIndex].ToString();
if (!crossTabColumnValues.Contains(crossTabColValue))
{
crossTabColumnValues.Add(crossTabColValue);
}
colIndex++; //skip the cross tabulation column
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
Object value = dataView[rowIndex].Row[i + colIndex];
dataMap.Add(key + crossTabColValue + i, value); // key = append all group by column + cross Tab Column + summarize column Index
}
}
if (prevGroupByColumnValue == null || !groupByColumnValue.SequenceEqual(prevGroupByColumnValue))
{
groupByColumnValueList.Add(groupByColumnValue);
}
SQLBuilder.Clauses.CrossTabulationClause crossTabClause = queryBuilder.CrossTabClause;
if (crossTabClause.SortSet)
{
crossTabColumnValues.Sort();
if (crossTabClause.SortOrder == Sorting.Descending)
{
crossTabColumnValues.Reverse();
}
}
crossTabResults.CrossTabColumnVaues = crossTabColumnValues;
totalCols = crossTabResults.GroupByColumns.Count + (crossTabResults.SummarizeColumns.Count * (crossTabResults.CrossTabColumnVaues.Count + 1));
// Populate missing values and Totals
int summarizColStartIndex = crossTabResults.GroupByColumns.Count + 1; // +1 for cross tabulation column (ehich is also part of groupby column in final query
for (int keyIndex = 0; keyIndex < keyPrefixes.Count; keyIndex++)
{
string keyPrefix = keyPrefixes.ElementAt <string>(keyIndex);
decimal[] totals = new decimal[crossTabResults.SummarizeColumns.Count];
for (int crosTabColValueIndex = 0; crosTabColValueIndex < crossTabResults.CrossTabColumnVaues.Count; crosTabColValueIndex++)
{
string crossTabColValue = crossTabResults.CrossTabColumnVaues.ElementAt <string>(crosTabColValueIndex);
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
string keyValue = keyPrefix + crossTabColValue + i;
string valueType = columnTypes.ElementAt <string>(summarizColStartIndex + i);
SQLBuilder.Clauses.Column summarizeColumn = crossTabResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(i);
if (dataMap.ContainsKey(keyValue))
{
//Add column values
Object value = dataMap[keyValue];
if (!(value == null || "".Equals(value.ToString().Trim())))
{
totals[i] = summarize(summarizeColumn.Name.Substring(0, summarizeColumn.Name.IndexOf('(')), totals[i], decimal.Parse(value.ToString()));
}
}
else
{
Object zeroValueObject = getZeroValueObject(valueType);
dataMap.Add(keyValue, zeroValueObject);
}
}
}
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
dataMap.Add(keyPrefix + "Grand Total" + i, getValueObject(totals[i], columnTypes[summarizColStartIndex + i]));
}
}
crossTabResults.DataMap = dataMap;
crossTabResults.GroupByColumnValueList = groupByColumnValueList;
crossTabResults.KeyPrefixes = keyPrefixes;
Console.WriteLine("Total time in Cross Tabulation execution: " + (DateTime.Now - startTime));
return(crossTabResults);
}
finally
{
connection.Close();
}
}
public static void CreateCrossTabulation(string filePath, DataTable dt, List <SQLBuilder.Clauses.Column> SelectedColumns, ResultViewModel rvm)
{
FileInfo f = new FileInfo(filePath);
if (f.Exists)
{
f.Delete();
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = (spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()));
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
string cl = "A";
uint row = 1;
int index;
Cell cell = new Cell();
System.Windows.Controls.Label colRow = new System.Windows.Controls.Label();
int lineNumber = 0;
colRow.Content = "Cross Tabulation by " + rvm.QueryBulder.CrossTabulationResults.CrossTabColumn.AliasName;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(colRow.Content));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
row++;
List <List <string> > groupByColumnValues = rvm.QueryBulder.CrossTabulationResults.GroupByColumnValueList;
for (int groupByColIndex = 0; groupByColIndex < rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
{
SQLBuilder.Clauses.Column groupByCol = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.ElementAt <SQLBuilder.Clauses.Column>(groupByColIndex);
string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
colRow.Content = Common.getColumnNameOrAlias(groupByCol);
for (int i = 0; i <= groupByColumnValues.Count + 1; i++)
{
if (groupByColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((groupByColIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + groupByColIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + groupByColIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[groupByColIndex].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(dt.Columns[groupByColIndex]));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(groupByColumnValues.ElementAt <List <string> >(i - 2).ElementAt <string>(groupByColIndex)));
}
lineNumber = lineNumber + 1;
row++;
}
row = 2;
}
int groupByColCount = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count;
int summarrizeValueIndex = groupByColCount + 1;
Dictionary <string, Object> dataMap = rvm.QueryBulder.CrossTabulationResults.DataMap;
row = 1;
foreach (string summaryMainValue in rvm.QueryBulder.CrossTabulationResults.CrossTabColumnVaues)
{
CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
summaryMain.lblSummaryHeader.Content = summaryMainValue;
int totalcolumn = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count + groupByColCount;
for (int summaryColIndex = 0; summaryColIndex < rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
{
SQLBuilder.Clauses.Column summaryCol = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(summaryColIndex);
string summaryColName = summaryCol.AliasName;
string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
for (int keyIndex = 0; keyIndex <= rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2; keyIndex++)
{
if (summaryColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((summarrizeValueIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
if (row == 1)
{
index = InsertSharedStringItem(summaryMainValue, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryMainValue));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row == 2)
{
index = InsertSharedStringItem(summaryColName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryColName));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
string key = rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.ElementAt <string>(keyIndex - 3);
string keyValue = key + summaryMainValue + summaryColIndex;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
if (dataMap.ContainsKey(keyValue))
{
if (summarycolFormat != null)
{
cell.CellValue = new CellValue(String.Format(summarycolFormat, dataMap[keyValue]));
}
else
{
cell.CellValue = new CellValue(Convert.ToString(dataMap[keyValue]));
}
}
else
{
cell.CellValue = new CellValue(Convert.ToString("00"));
}
}
lineNumber = lineNumber + 1;
if (row > rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2)
{
row = 1;
summarrizeValueIndex = summarrizeValueIndex + 1;
}
else
{
row++;
}
}
lineNumber = 0;
}
row = 1;
groupByColCount = groupByColCount + 1;
}
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
private List<SQLBuilder.Clauses.Column> GenerateListOfTabulationTabColumns_old()
{
List<SQLBuilder.Clauses.Column> list = new List<SQLBuilder.Clauses.Column>();
//get the From table columns
if (this.listOfTable != null & this.listOfTable.Count > 0)
{
if (this.FromTabUC.cmbFromTable.SelectedIndex != -1)
{
foreach (Column col in this.listOfTable[this.FromTabUC.cmbFromTable.SelectedIndex].columns)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();//false, this.FromTabUC.txtFromAlias.Text + "." + col.name, "");
sc.Name = this.FromTabUC.txtFromAlias.Text + "." + col.name;
sc.Format = col.format;
sc.DataType = col.type;
list.Add(sc);
}
}
}
// get join table columns
if (this.FromTabUC.StackPanelFromTab.Children.Count > 0)
{
for (int i = 0; i < this.FromTabUC.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)this.FromTabUC.StackPanelFromTab.Children[i];
foreach (Column col in this.listOfTable[fs.cmbFromTabJoinTable.SelectedIndex].columns)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();
sc.Name = fs.txtJoinTableAlias.Text + "." + col.name;
sc.Format = col.format;
sc.DataType = col.type;
list.Add(sc);
}
}
}
return list;
}
//zahed
public SelectQueryBuilder LoadSelectQueryBuilderNew(ResultViewControl rc) //made public zahed
{
SelectQueryBuilder query = new SelectQueryBuilder();
bool isSelecteTabColumnsOrGroupbyColumns = false;
if (rc.SelectTabCntrl.lstSelectedCol.ItemsSource != null)
{
//****Geting select columns*****
ObservableCollection<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((ObservableCollection<SQLBuilder.Clauses.Column>)rc.SelectTabCntrl.lstSelectedCol.ItemsSource);
query.SelectedColumns = ListOfSelectColumn.ToList();
//foreach (SQLBuilder.Clauses.Column Col in ListOfSelectColumn)
//{
// SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
// column.Name = Col.Name;
// column.AliasName = Col.AliasName;
// column.Format = Col.Format;
// query.SelectColumn(column);
//}
isSelecteTabColumnsOrGroupbyColumns = true;
}
if (rc.TabulationTabCntrl.isTabulation)
{
//group by column
for (int i = 0; i < rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[i];
if (tg.cmbTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = tg.cmbTabulationTabGroupByColumnsName.Text;
column.Format = tg.txtTabulationTabGroupByColFormat.Text;
column.AliasName = tg.txtTabulationTabGroupByAlias.Text;
query.AddGroupBy(column);
if (tg.cmbTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(tg.cmbTabulationTabGroupByColumnsName.Text, GetSortingEnum(tg.cmbTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children[i];
if (ts.cmbTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbTabulationTypeOfSummary.Text + "(" + ts.cmbTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
else if (rc.CrossTabulationTabCntrl.isCrossTabulation)
{
//group by column
for (int i = 0; i < rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
//column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;
column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;//((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Name;
column.Format = ctg.txtCrossTabulationTabGroupByColFormat.Text; //((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Format;
column.AliasName = ctg.txtCrossTabulationTabGroupByAlias.Text;
//query.SelectColumn(column);
query.AddGroupBy(column);
if (ctg.cmbCrossTabulationSort.SelectedIndex != -1)
{
// SQLBuilder.Clauses.OrderByClause orderBy = new SQLBuilder.Clauses.OrderByClause(tg.cmbTabulationTabGroupByColumnsName.Text,GetSortingEnum(tg.cmbTabulationSort.Text));
// modified on 11/18/11
query.AddOrderBy(ctg.cmbCrossTabulationTabGroupByColumnsName.Text, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
//query.AddOrderBy(((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Name, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ts.cmbCrossTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtCrossTabulationTabSummaryColFormat.Text;
//column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ((SQLBuilder.Clauses.Column)ts.cmbCrossTabulationTabSummaryColumnsName.SelectionBoxItem).Name + ")";
//column.Format = ((SQLBuilder.Clauses.Column)ts.cmbCrossTabulationTabSummaryColumnsName.SelectionBoxItem).Format;
column.AliasName = ts.txtCrossTabulationTabSummaryAlias.Text;
//query.SelectColumn(column);
query.AddSummarize(column);
}
}
//summary first row
SQLBuilder.Clauses.Column column1 = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column1.Name = rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text;
//column1.Name = ((SQLBuilder.Clauses.Column)this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectionBoxItem).Name;
if (rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex == -1)
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1);
isSelecteTabColumnsOrGroupbyColumns = true;
}
else
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1, GetSortingEnum(rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.Text));
isSelecteTabColumnsOrGroupbyColumns = true;
}
}
if (isSelecteTabColumnsOrGroupbyColumns)
{
//Geting from table
SQLBuilder.Clauses.Table fromTable = null;
MySQLData.Table table = this.listOfTable[rc.FromTabCntrl.cmbFromTable.SelectedIndex];
if (table is MySQLData.DerivedTable)
{
fromTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)table, rc.FromTabCntrl.txtFromAlias.Text);
}
else if (table is MySQLData.Table)
{
fromTable = new SQLBuilder.Clauses.Table(table, rc.FromTabCntrl.txtFromAlias.Text);
}
//fromTable.Name = rc.FromTabCntrl.cmbFromTable.Text;
//fromTable.AliasName = rc.FromTabCntrl.txtFromAlias.Text;
query.SelectFromTable(fromTable);
//Geting join table and join colums
for (int i = 0; i < rc.FromTabCntrl.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)rc.FromTabCntrl.StackPanelFromTab.Children[i];
SQLBuilder.Clauses.Table joinTable = null;
MySQLData.Table jTable = null;
if (fs.cmbFromTabJoinTable.SelectedIndex != -1)
{
jTable = this.listOfTable[fs.cmbFromTabJoinTable.SelectedIndex];
if (jTable is MySQLData.DerivedTable)
{
joinTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)jTable, fs.txtJoinTableAlias.Text);
}
else if (jTable is MySQLData.Table)
{
joinTable = new SQLBuilder.Clauses.Table(jTable, fs.txtJoinTableAlias.Text);
}
//joinTable.Name = fs.cmbFromTabJoinTable.Text;
//joinTable.AliasName = fs.txtJoinTableAlias.Text;
SQLBuilder.Clauses.JoinClause joinClause = query.AddJoin(GetJoinType(fs.cmbFromTabJoinType.Text), fromTable, fs.cmbFromTabFromColumns.Text, GetComparisonOpreator(fs.cmbFromTabQueryOpretor.Text), joinTable, fs.cmbFromTabJoinColumns.Text);
for (int condIndex = 0; condIndex < fs.StackPanelFromTabMore.Children.Count; condIndex++)
{
FromTabStackPanelControlMore fsCondition = (FromTabStackPanelControlMore)fs.StackPanelFromTabMore.Children[condIndex];
joinClause.addJoinCondition(GetLogicalOpreator(fsCondition.cmbFromTabFromANDOR.Text), fromTable, fsCondition.cmbFromTabFromColumns.Text, GetComparisonOpreator(fsCondition.cmbFromTabQueryOpretor.Text), joinTable, fsCondition.cmbFromTabJoinColumns.Text);
}
}
}
//******Geting Wheree Clause********
for (int i = 0; i < rc.WhereTabCntrl.StackPanelWhereTab.Children.Count; i++)
{
string controlType = rc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().ToString();
switch (controlType)
{
case "FastDB.Control.WhereTabRegularConditionControl":
WhereTabRegularConditionControl ws = (WhereTabRegularConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(ws.cmbWhereTabQueryAndOr.Text), ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabBetweenConditionControl":
WhereTabBetweenConditionControl wsb = (WhereTabBetweenConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wsb.cmbWhereTabQueryAndOr.Text), wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabInNotInConditionControl":
WhereTabInNotInConditionControl wInNotIn = (WhereTabInNotInConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wInNotIn.cmbWhereTabQueryAndOr.Text), wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabNullNotNullConditionControl":
WhereTabNullNotNullConditionControl wNullNotNull = (WhereTabNullNotNullConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wNullNotNull.cmbWhereTabQueryAndOr.Text), wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
break;
}
}
}
return query;
}
private List<SQLBuilder.Clauses.Column> GenerateListOfTabulationTabColumns()
{
if (this.SelectTabUC._SelectedColCollection.Count > 0)
{
List<SQLBuilder.Clauses.Column> list = new List<SQLBuilder.Clauses.Column>();
foreach (SQLBuilder.Clauses.Column column in this.SelectTabUC._SelectedColCollection)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();
sc.Name = column.AliasName;
sc.Format = column.Format;
sc.DataType = column.DataType;
list.Add(sc);
}
return list;
}
else
{
return GenerateListOfSelectTabColumns();
}
}
public List<SQLBuilder.Clauses.Column> GenerateListOfTabulationTabCntrlColumns(ResultViewControl rc)
{
if (rc.SelectTabCntrl._SelectedColCollection.Count > 0)
{
List<SQLBuilder.Clauses.Column> list = new List<SQLBuilder.Clauses.Column>();
foreach (SQLBuilder.Clauses.Column column in rc.SelectTabCntrl._SelectedColCollection)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();
sc.Name = column.AliasName;//column.Name;
sc.Format = column.Format;
sc.DataType = column.DataType;
list.Add(sc);
}
return list;
}
else
{
return GenerateListOfSelectTabCntrlColumns(rc);
}
}
public List<SQLBuilder.Clauses.Column> GenerateListOfSelectTabCntrlColumns(ResultViewControl rc)
{
List<SQLBuilder.Clauses.Column> list = new List<SQLBuilder.Clauses.Column>();
//get the From table columns
if (this.listOfTable != null & this.listOfTable.Count > 0)
{
if (rc.FromTabCntrl.cmbFromTable.SelectedIndex != -1)
{
foreach (Column col in this.listOfTable[rc.FromTabCntrl.cmbFromTable.SelectedIndex].columns)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();//false, this.FromTabUC.txtFromAlias.Text + "." + col.name, "");
sc.Name = rc.FromTabCntrl.txtFromAlias.Text + "." + col.name;
sc.AliasName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(col.name);
sc.Format = col.format;
sc.DataType = col.type;
list.Add(sc);
}
}
}
// get join table columns
if (rc.FromTabCntrl.StackPanelFromTab.Children.Count > 0)
{
for (int i = 0; i < rc.FromTabCntrl.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)rc.FromTabCntrl.StackPanelFromTab.Children[i];
if (fs.cmbFromTabJoinTable.SelectedIndex > 0)
{
foreach (Column col in this.listOfTable[fs.cmbFromTabJoinTable.SelectedIndex].columns)
{
SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();
sc.Name = fs.txtJoinTableAlias.Text + "." + col.name;
sc.AliasName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(fs.txtJoinTableAlias.Text + " " + col.name);
sc.Format = col.format;
sc.DataType = col.type;
list.Add(sc);
}
}
//if (fs.cmbFromTabJoinTableMore.SelectedIndex > 0)
//{
// foreach (Column col in this.listOfTable[fs.cmbFromTabJoinTableMore.SelectedIndex].columns)
// {
// SQLBuilder.Clauses.Column sc = new SQLBuilder.Clauses.Column();
// sc.Name = fs.txtJoinTableAlias.Text + "." + col.name;
// sc.Format = col.format;
// list.Add(sc);
// }
//}
}
}
return list;
}
public SelectQueryBuilder LoadSelectQueryBuilder() //made public zahed
{
SelectQueryBuilder query = new SelectQueryBuilder();
bool isSelecteTabColumnsOrGroupbyColumns = false;
if (this.SelectTabUC.lstSelectedCol.ItemsSource != null)
{
//****Geting select columns*****
//List<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((List<SQLBuilder.Clauses.Column>)this.SelectTabUC.lstSelectedCol.ItemsSource);
ObservableCollection<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((ObservableCollection<SQLBuilder.Clauses.Column>)this.SelectTabUC.lstSelectedCol.ItemsSource);
foreach (SQLBuilder.Clauses.Column Col in ListOfSelectColumn)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = Col.Name;
column.AliasName = Col.AliasName;
column.Format = Col.Format;
query.SelectColumn(column);
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
if (this.TabulationTabUC.isTabulation)
{
//group by column
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[i];
if (tg.cmbTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column.Name = tg.cmbTabulationTabGroupByColumnsName.Text;
column.Format = tg.txtTabulationTabGroupByColFormat.Text;
column.AliasName = tg.txtTabulationTabGroupByAlias.Text;
//query.SelectColumn(column);
query.AddGroupBy(column);
if (tg.cmbTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(tg.cmbTabulationTabGroupByColumnsName.Text, GetSortingEnum(tg.cmbTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[i];
if (ts.cmbTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbTabulationTypeOfSummary.Text + "(" + ts.cmbTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
else if (this.CrossTabulationTabUC.isCrossTabulation)
{
//group by column
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;
column.Format = ctg.txtCrossTabulationTabGroupByColFormat.Text;
column.AliasName = ctg.txtCrossTabulationTabGroupByAlias.Text;
query.AddGroupBy(column);
if (ctg.cmbCrossTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(ctg.cmbCrossTabulationTabGroupByColumnsName.Text, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ts.cmbCrossTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtCrossTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtCrossTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
//summary first row
SQLBuilder.Clauses.Column column1 = new SQLBuilder.Clauses.Column();
column1.Name = this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text;
if (this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex == -1)
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1);
isSelecteTabColumnsOrGroupbyColumns = true;
}
else
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1, GetSortingEnum(this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.Text));
isSelecteTabColumnsOrGroupbyColumns = true;
}
}
if (isSelecteTabColumnsOrGroupbyColumns)
{
//Geting from table
SQLBuilder.Clauses.Table fromTable = new SQLBuilder.Clauses.Table();
fromTable.Name = this.FromTabUC.cmbFromTable.Text;
fromTable.AliasName = this.FromTabUC.txtFromAlias.Text;
query.SelectFromTable(fromTable);
for (int i = 0; i < this.FromTabUC.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)this.FromTabUC.StackPanelFromTab.Children[i];
SQLBuilder.Clauses.Table joinTable = new SQLBuilder.Clauses.Table();
joinTable.Name = fs.cmbFromTabJoinTable.Text;
joinTable.AliasName = fs.txtJoinTableAlias.Text;
query.AddJoin(GetJoinType(fs.cmbFromTabJoinType.Text), joinTable, fs.cmbFromTabJoinColumns.Text, GetComparisonOpreator(fs.cmbFromTabQueryOpretor.Text), fromTable, fs.cmbFromTabFromColumns.Text);
}
//******Geting Wheree Clause********
for (int i = 0; i < this.WhereTabUC.StackPanelWhereTab.Children.Count; i++)
{
string controlType = this.WhereTabUC.StackPanelWhereTab.Children[i].GetType().ToString();
switch (controlType)
{
case "FastDB.Control.WhereTabRegularConditionControl":
WhereTabRegularConditionControl ws = (WhereTabRegularConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(ws.cmbWhereTabQueryAndOr.Text), ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabBetweenConditionControl":
WhereTabBetweenConditionControl wsb = (WhereTabBetweenConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wsb.cmbWhereTabQueryAndOr.Text), wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabInNotInConditionControl":
WhereTabInNotInConditionControl wInNotIn = (WhereTabInNotInConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wInNotIn.cmbWhereTabQueryAndOr.Text), wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabNullNotNullConditionControl":
WhereTabNullNotNullConditionControl wNullNotNull = (WhereTabNullNotNullConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wNullNotNull.cmbWhereTabQueryAndOr.Text), wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
break;
}
}
}
return query;
}
private void PopulateCrossTabulationViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder)
{
//display CrossTabulation header
this.lblSummaryMainColumnName.Content = "Cross Tabulation by " + QuerryBuilder.CrossTabulationResults.CrossTabColumn.AliasName;
// adding gropby column control
int lineNumber = 0;
List <List <string> > groupByColumnValues = QuerryBuilder.CrossTabulationResults.GroupByColumnValueList;
int goupbyColumValueIndex = 0;
for (int groupByColIndex = 0; groupByColIndex < QuerryBuilder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
{
SQLBuilder.Clauses.Column groupByCol = QuerryBuilder.CrossTabulationResults.GroupByColumns.ElementAt <SQLBuilder.Clauses.Column>(groupByColIndex);
// find colum format
string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
ctvgCntrl.lblGroupByColumnHeader.Content = Common.getColumnNameOrAlias(groupByCol);
for (int i = 0; i < groupByColumnValues.Count; i++)
{
Label colRow = new Label();
if ((lineNumber % 2) == 0)
{
colRow.Style = (Style)FindResource("CrossTabulationEvenGroupByColumRowStyle");
}
else
{
colRow.Style = (Style)FindResource("CrossTabulationOddGroupByColumRowStyle");
}
if (colFormat != null)
{
//formating group by col value
colRow.Content = String.Format(colFormat, groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex));
}
else
{
colRow.Content = groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex);
}
ctvgCntrl.StackPaenlGroupbyColumnRows.Children.Add(colRow);
lineNumber = lineNumber + 1;
}
lineNumber = 0;
this.StackPanelCrossTabulationViewGroupByControls.Children.Add(ctvgCntrl);
goupbyColumValueIndex = goupbyColumValueIndex + 1;
}
lineNumber = 0;
int groupByColCount = QuerryBuilder.CrossTabulationResults.GroupByColumns.Count;
int summarrizeValueIndex = groupByColCount;
// adding summary main control and summary column control
// add Grand Total to CrossTabColumnVaues as las item
Dictionary <string, Object> dataMap = QuerryBuilder.CrossTabulationResults.DataMap;
QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues.Add("Grand Total");
foreach (string summaryMainValue in QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues)
{
CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
summaryMain.lblSummaryHeader.Content = summaryMainValue;
for (int summaryColIndex = 0; summaryColIndex < QuerryBuilder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
{
SQLBuilder.Clauses.Column summaryCol = QuerryBuilder.CrossTabulationResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(summaryColIndex);
// find column format
string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
ctvsCtrl.lblSummaryColumnHeader.Content = " " + Common.getColumnNameOrAlias(summaryCol) + " ";
//chnageing background color for last grad total section
if (summaryMainValue == "Grand Total")
{
summaryMain.lblSummaryHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
ctvsCtrl.lblSummaryColumnHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle");
}
for (int keyIndex = 0; keyIndex < QuerryBuilder.CrossTabulationResults.KeyPrefixes.Count; keyIndex++)
{
string key = QuerryBuilder.CrossTabulationResults.KeyPrefixes.ElementAt <string>(keyIndex);
Label colRow = new Label();
if ((lineNumber % 2) == 0)
{
if (summaryMainValue == "Grand Total")
{
colRow.Style = (Style)FindResource("CrossTabulationGrandTotalSummaryColumRowStyle");
}
else
{
colRow.Style = (Style)FindResource("CrossTabulationEvenSummaryColumRowStyle");
}
}
else
{
colRow.Style = (Style)FindResource("CrossTabulationOddSummaryColumRowStyle");
}
string keyValue = key + summaryMainValue + summaryColIndex;
if (dataMap.ContainsKey(keyValue))
{
if (summarycolFormat != null)
{
colRow.Content = String.Format(summarycolFormat, dataMap[keyValue]);
}
else
{
colRow.Content = dataMap[keyValue].ToString();
}
}
else
{
colRow.Content =
"";
}
DockPanel dp = new DockPanel();
dp.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
dp.VerticalAlignment = System.Windows.VerticalAlignment.Stretch;
dp.Children.Add(colRow);
ctvsCtrl.StackPaenlSummaryColumnRows.Children.Add(dp);
lineNumber = lineNumber + 1;
}
lineNumber = 0;
summaryMain.StackPaenlSummaryMainColumns.Children.Add(ctvsCtrl);
summarrizeValueIndex = summarrizeValueIndex + 1;
}
this.StackPanelCrossTabulationViewSummaryControls.Children.Add(summaryMain);
}
}
