setSelectedColumns_CrossTabulation(string connectionString)
{
Column crossTabColumn = CrossTabClause.Col;
List <Column> columns = new List <Column>();
List <Column> summarizeColumns = new List <Column>();
//add summarize columns to the list
//for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
//{
// summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i));
//}
_crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, _summarizeColumns);
CrossTabClause.SelectedColumns = columns;
}
setSelectedColumns_CrossTabulation(string connectionString)
{
Column crossTabColumn = CrossTabClause.Col;
List<Column> columns = new List<Column>();
List<Column> summarizeColumns = new List<Column>();
//add summarize columns to the list
//for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
//{
// summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i));
//}
_crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, _summarizeColumns);
CrossTabClause.SelectedColumns = columns;
}
//public void setSelectedColumns_CrossTabulation(string connectionString)
//{
// Column crossTabColumn = CrossTabClause.Col;
// string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect();
// //_selectedTables.ElementAt<Table>(0).Name + " " +_selectedTables.ElementAt<Table>(0).AliasName;
// if (CrossTabClause.SortSet)
// {
// if (CrossTabClause.SortOrder == Sorting.Ascending)
// {
// crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC";
// }
// else if (CrossTabClause.SortOrder == Sorting.Descending)
// {
// crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC";
// }
// }
// crossTabColQuery += ";";
// MySqlConnection connection = new MySqlConnection(connectionString);
// connection.Open();
// try
// {
// List<Column> columns = new List<Column>();
// //ADD groupby columns to select columns
// for (int i = 0; i < _groupByColumns.Count; i++)
// {
// Column c = _groupByColumns.ElementAt<Column>(i);
// columns.Add(c);
// }
// DataSet dataSet = new DataSet();
// MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection);
// dataAdapter.Fill(dataSet);
// DataRowCollection rows = dataSet.Tables[0].Rows;
// List<string> crossTabValues = new List<string>();
// List<Column> summarizeColumns = new List<Column>();
// for (int i = 0; i <= rows.Count; i++)
// {
// String name = null;
// if (i < rows.Count)
// {
// DataRow row = rows[i];
// if (row.ItemArray[0] != System.DBNull.Value)
// {
// //name = (string)row.ItemArray[0];
// name = "" + row.ItemArray[0];
// }
// crossTabValues.Add(name);
// }
// for (int colIndex = 0; colIndex < _selectedColumns.Count; colIndex++)
// {
// Column currentCol = _selectedColumns.ElementAt<Column>(colIndex);
// Column col = new Column();
// int ColNameIndex = currentCol.Name.IndexOf('(')+1;
// if (ColNameIndex > 0)
// {
// string summuryFunction = currentCol.Name.Substring(0, ColNameIndex);
// string colName = currentCol.Name.Substring(ColNameIndex, (currentCol.Name.IndexOf(')') - ColNameIndex));
// //SUM(IF(grade_mic='A-',close_bal,0)) 'A- Closing Balance',
// if (i < rows.Count)
// {
// if (name != null)
// {
// name = name.Replace("'", "''");
// if (summuryFunction.ToUpper().StartsWith("COUNT"))
// {
// col.Name = "SUM(" + "IF(" + crossTabColumn.Name + "='" + name + "'," + "1,0))";
// }
// else
// {
// col.Name = summuryFunction + "IF(" + crossTabColumn.Name + "='" + name + "'," + colName + ",0))";
// }
// //col.AliasName = name + " " + currentCol.AliasName;
// col.AliasName = currentCol.AliasName;
// }
// else
// {
// if (summuryFunction.ToUpper().StartsWith("COUNT"))
// {
// col.Name = "SUM(" + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + "1,0))";
// }
// else
// {
// col.Name = summuryFunction + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + colName + ",0))";
// }
// col.AliasName = "NULL " + currentCol.AliasName;
// }
// }
// else
// {
// col.Name = currentCol.Name;
// col.AliasName = currentCol.AliasName;
// }
// columns.Add(col);
// }
// }
// }
// //add summarize columns to the list
// for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
// {
// summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i));
// }
// _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues);
// CrossTabClause.SelectedColumns = columns;
// //_selectedColumns.Clear();
// //_selectedColumns = columns;
// }
// finally
// {
// connection.Close();
// }
//}
public void setSelectedColumns_CrossTabulation_old(string connectionString)
{
Column crossTabColumn = CrossTabClause.Col;
string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect();
if (CrossTabClause.SortSet)
{
if (CrossTabClause.SortOrder == Sorting.Ascending)
{
crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC";
}
else if (CrossTabClause.SortOrder == Sorting.Descending)
{
crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC";
}
}
crossTabColQuery += ";";
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
List <Column> columns = new List <Column>();
//ADD groupby columns to select columns
//for (int i = 0; i < _groupByColumns.Count; i++)
//{
// Column c = _groupByColumns.ElementAt<Column>(i);
// columns.Add(c);
//}
//columns.Add(crossTabColumn);
DataSet dataSet = new DataSet();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection);
dataAdapter.Fill(dataSet);
DataRowCollection rows = dataSet.Tables[0].Rows;
List <string> crossTabValues = new List <string>();
List <Column> summarizeColumns = new List <Column>();
for (int i = 0; i < rows.Count; i++)
{
String name = null;
if (i < rows.Count)
{
DataRow row = rows[i];
if (row.ItemArray[0] != System.DBNull.Value)
{
//name = (string)row.ItemArray[0];
name = "" + row.ItemArray[0];
}
crossTabValues.Add(name);
}
}
//add summarize columns to the list
for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
{
summarizeColumns.Add(_selectedColumns.ElementAt <Column>(i));
}
_crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues);
CrossTabClause.SelectedColumns = columns;
}
finally
{
connection.Close();
}
}
//public void setSelectedColumns_CrossTabulation(string connectionString)
//{
// Column crossTabColumn = CrossTabClause.Col;
// string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect();
// //_selectedTables.ElementAt<Table>(0).Name + " " +_selectedTables.ElementAt<Table>(0).AliasName;
// if (CrossTabClause.SortSet)
// {
// if (CrossTabClause.SortOrder == Sorting.Ascending)
// {
// crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC";
// }
// else if (CrossTabClause.SortOrder == Sorting.Descending)
// {
// crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC";
// }
// }
// crossTabColQuery += ";";
// MySqlConnection connection = new MySqlConnection(connectionString);
// connection.Open();
// try
// {
// List<Column> columns = new List<Column>();
// //ADD groupby columns to select columns
// for (int i = 0; i < _groupByColumns.Count; i++)
// {
// Column c = _groupByColumns.ElementAt<Column>(i);
// columns.Add(c);
// }
// DataSet dataSet = new DataSet();
// MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection);
// dataAdapter.Fill(dataSet);
// DataRowCollection rows = dataSet.Tables[0].Rows;
// List<string> crossTabValues = new List<string>();
// List<Column> summarizeColumns = new List<Column>();
// for (int i = 0; i <= rows.Count; i++)
// {
// String name = null;
// if (i < rows.Count)
// {
// DataRow row = rows[i];
// if (row.ItemArray[0] != System.DBNull.Value)
// {
// //name = (string)row.ItemArray[0];
// name = "" + row.ItemArray[0];
// }
// crossTabValues.Add(name);
// }
// for (int colIndex = 0; colIndex < _selectedColumns.Count; colIndex++)
// {
// Column currentCol = _selectedColumns.ElementAt<Column>(colIndex);
// Column col = new Column();
// int ColNameIndex = currentCol.Name.IndexOf('(')+1;
// if (ColNameIndex > 0)
// {
// string summuryFunction = currentCol.Name.Substring(0, ColNameIndex);
// string colName = currentCol.Name.Substring(ColNameIndex, (currentCol.Name.IndexOf(')') - ColNameIndex));
// //SUM(IF(grade_mic='A-',close_bal,0)) 'A- Closing Balance',
// if (i < rows.Count)
// {
// if (name != null)
// {
// name = name.Replace("'", "''");
// if (summuryFunction.ToUpper().StartsWith("COUNT"))
// {
// col.Name = "SUM(" + "IF(" + crossTabColumn.Name + "='" + name + "'," + "1,0))";
// }
// else
// {
// col.Name = summuryFunction + "IF(" + crossTabColumn.Name + "='" + name + "'," + colName + ",0))";
// }
// //col.AliasName = name + " " + currentCol.AliasName;
// col.AliasName = currentCol.AliasName;
// }
// else
// {
// if (summuryFunction.ToUpper().StartsWith("COUNT"))
// {
// col.Name = "SUM(" + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + "1,0))";
// }
// else
// {
// col.Name = summuryFunction + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + colName + ",0))";
// }
// col.AliasName = "NULL " + currentCol.AliasName;
// }
// }
// else
// {
// col.Name = currentCol.Name;
// col.AliasName = currentCol.AliasName;
// }
// columns.Add(col);
// }
// }
// }
// //add summarize columns to the list
// for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
// {
// summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i));
// }
// _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues);
// CrossTabClause.SelectedColumns = columns;
// //_selectedColumns.Clear();
// //_selectedColumns = columns;
// }
// finally
// {
// connection.Close();
// }
//}
public void setSelectedColumns_CrossTabulation_old(string connectionString)
{
Column crossTabColumn = CrossTabClause.Col;
string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect();
if (CrossTabClause.SortSet)
{
if (CrossTabClause.SortOrder == Sorting.Ascending)
{
crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC";
}
else if (CrossTabClause.SortOrder == Sorting.Descending)
{
crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC";
}
}
crossTabColQuery += ";";
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
List<Column> columns = new List<Column>();
//ADD groupby columns to select columns
//for (int i = 0; i < _groupByColumns.Count; i++)
//{
// Column c = _groupByColumns.ElementAt<Column>(i);
// columns.Add(c);
//}
//columns.Add(crossTabColumn);
DataSet dataSet = new DataSet();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection);
dataAdapter.Fill(dataSet);
DataRowCollection rows = dataSet.Tables[0].Rows;
List<string> crossTabValues = new List<string>();
List<Column> summarizeColumns = new List<Column>();
for (int i = 0; i < rows.Count; i++)
{
String name = null;
if (i < rows.Count)
{
DataRow row = rows[i];
if (row.ItemArray[0] != System.DBNull.Value)
{
//name = (string)row.ItemArray[0];
name = "" + row.ItemArray[0];
}
crossTabValues.Add(name);
}
}
//add summarize columns to the list
for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++)
{
summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i));
}
_crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues);
CrossTabClause.SelectedColumns = columns;
}
finally
{
connection.Close();
}
}
