public static CrossTabResults getCrossTabulationData_old(string connectionString, SelectQueryBuilder queryBuilder, out int totalItems, out int totalCols)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
string queryWithoutSelect = queryBuilder.getQueryforGroupBy(out totalCols);
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
string selectPartQuery = queryBuilder.getCrossTabSelectPartQuery();
DataSet dataSet = new DataSet();
totalItems = 0;
CrossTabResults crossTabResults = queryBuilder.CrossTabulationResults;
totalCols = crossTabResults.GroupByColumns.Count + (crossTabResults.SummarizeColumns.Count * (crossTabResults.CrossTabColumnVaues.Count + 1));
if (queryBuilder.GroupByColumns.Count() == 0)
{
throw new Exception("Calling wrong method!!!");
}
else
{
string finalQuery = selectPartQuery + " " + queryWithoutSelect + ";";
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;
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();
colIndex++;
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);
}
// 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
{
// set the missing column value to 0
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();
}
}
