public ReportDictionary RunFinalQuery(UserCreatedQueryObject FinalQuery, string username)
{
// create static values to construct the SQL query
var arrayCount = FinalQuery.ColumnNames.Count;
var sqlSelect = @"SELECT ";
var sqlFrom = @" FROM cameo_DWH.dbo." + FinalQuery.TableName.ToString();
var queryName = FinalQuery.QueryName;
// create arrays to store the selected columns, where and group clauses (if any)
List <string> columnArray = new List <string>();
List <string> columnArrayForDictionary = new List <string>();
List <string> groupArray = new List <string>();
List <string> whereArray = new List <string>();
// iterate through the list of columns and add each value to the correct array
for (var i = 0; i < arrayCount; i++)
{
if (FinalQuery.AdditionalActions[i].columnsToAggregate == "None")
{
if (FinalQuery.IncludeInViews[i].columnsToInclude == "Yes")
{
columnArray.Add("ISNULL(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ",'') AS " + FinalQuery.ColumnNames[i].ColumnName.ToString());
groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString());
columnArrayForDictionary.Add(FinalQuery.ColumnNames[i].ColumnName.ToString());
}
}
else
{
//columnArray.Add(FinalQuery.AdditionalActions[i].columnsToAggregate.ToString() + "(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ") AS " + FinalQuery.ColumnNames[i].ColumnName.ToString());
columnArrayForDictionary.Add(FinalQuery.ColumnNames[i].ColumnName.ToString());
columnArray.Add(FinalQuery.AdditionalActions[i].columnsToAggregate.ToString() + "(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ") AS " + FinalQuery.ColumnNames[i].ColumnName.ToString());
//groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString());
//if (FinalQuery.IncludeInViews[i].columnsToInclude == "Yes")
//{
//groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString());
//}
}
if (FinalQuery.FilterOperators[i].FilterOperator != "None")
{
if (FinalQuery.FilterOperators[i].FilterOperator == "=")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ FinalQuery.FilterOperators[i].FilterOperator.ToString() + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ FinalQuery.FilterOperators[i].FilterOperator.ToString() + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "not=")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<>" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(" WHERE " + FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<>" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "greaterThan")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ ">" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ ">" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "lessThan")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "greaterEqual")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ ">=" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ ">=" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "lessEqual")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<=" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'");
}
else
{
whereArray.Add(" WHERE " + FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "<=" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "between")
{
if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "BETWEEN" + " "
+ "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "' AND '" + FinalQuery.SecondFilterValue[i].SecondFilterValue.ToString() + "'");
}
else
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "BETWEEN" + " "
+ FinalQuery.FilterValues[i].FilterValue.ToString() + " AND " + FinalQuery.SecondFilterValue[i].SecondFilterValue.ToString());
}
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "contains")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "LIKE" + " "
+ "'%" + FinalQuery.FilterValues[i].FilterValue.ToString() + "%'");
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "isOneOf")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "IN" + " ('" + FinalQuery.FilterValues[i].FilterValue.ToString().Replace(",", "','") + "')");
}
else if (FinalQuery.FilterOperators[i].FilterOperator == "isNotOneOf")
{
whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " "
+ "NOT IN" + " ('" + FinalQuery.FilterValues[i].FilterValue.ToString().Replace(",", "','") + "')");
}
}
}
// Add comma seperators to the select section
foreach (var j in columnArray)
{
sqlSelect = sqlSelect + j + ",";
}
//System.Diagnostics.Debug.WriteLine(sqlSelect);
// add the FROM section of the SQL query
sqlSelect = sqlSelect.Remove(sqlSelect.Length - 1, 1) + sqlFrom;
// add WHERE clause values if any exist and remove the additional AND from the end of the string
if (whereArray.Count > 0)
{
sqlSelect += " WHERE ";
foreach (var g in whereArray)
{
sqlSelect = sqlSelect + g + " AND ";
}
sqlSelect = sqlSelect.Remove(sqlSelect.Length - 4, 3);
}
// add an optional GROUP BY clause
if (groupArray.Count > 0)
{
sqlSelect = sqlSelect + " GROUP BY ";
foreach (var h in groupArray)
{
sqlSelect = sqlSelect + h + ",";
}
}
// remove extra commas from the end of the string
sqlSelect = sqlSelect.Remove(sqlSelect.Length - 1, 1);
//System.Diagnostics.Debug.WriteLine(sqlSelect);
// create a multidimensional dictionary object
Dictionary <string, List <string> > results = new Dictionary <string, List <string> >();
// add keys to the dictionary (column headers)
if (columnArrayForDictionary.Count > 0)
{
for (var colArray = 0; colArray < columnArrayForDictionary.Count; colArray++)
{
//List<string> list = new List<string>();
List <string> arraystring = new List <string>();
results.Add(columnArrayForDictionary[colArray], arraystring);
}
}
// open a localised connection to SQL Server and pass in the generated query. Push each row returned to the dictionary values
using (SqlConnection conn = new SqlConnection("Data Source =OAKSQL03; Initial Catalog = Cameo_DWH; User Id=camarchive; Password=camarchive; Pooling=False; Connect Timeout = 30"))
{
SqlCommand comm = new SqlCommand(sqlSelect, conn);
comm.Connection.Open();
comm.ExecuteNonQuery();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Dictionary <string, List <string> > clm = new Dictionary <string, List <string> >();
for (int h = 0; h < columnArrayForDictionary.Count; h++)
{
dynamic fieldMetaData = new ExpandoObject();
var sqlType = reader.GetDataTypeName(h);
//var sqlColName = reader.GetName(h).First();
//System.Diagnostics.Debug.WriteLine(sqlColName);
if (sqlType == "varchar" || sqlType == "nvarchar")
{
results[reader.GetName(h)].Add(reader.GetString(h));
}
else if (sqlType == "bigint")
{
results[reader.GetName(h)].Add(reader.GetInt64(h).ToString());
}
else if (sqlType == "float")
{
results[reader.GetName(h)].Add(reader.GetFloat(h).ToString());
}
else if (sqlType == "int")
{
results[reader.GetName(h)].Add(reader.GetInt32(h).ToString());
}
else if (sqlType == "decimal")
{
results[reader.GetName(h)].Add(reader.GetDecimal(h).ToString());
}
else if (sqlType == "numeric")
{
results[reader.GetName(h)].Add(reader.GetDecimal(h).ToString());
}
else if (sqlType == "datetime")
{
if (reader.GetDateTime(h).ToString() == null)
{
results[reader.GetName(h)].Add("");
}
else
{
//results[reader.GetName(h)].Add(reader.GetDateTime(h).ToShortDateString().ToString());
results[reader.GetName(h)].Add(reader.GetDateTime(h).ToString());
}
}
}
}
comm.Connection.Close();
}
// pass the final dictionary object to the global dictionary
ReportDictionary report = new ReportDictionary();
report.ReportResult = results;
SQLString queryToSave = new SQLString();
queryToSave.StoreSql(sqlSelect, queryName, username);
// return a partial view to the JQuery request and pass in the dictionary class
return(report);
}