public FileResult DownloadReport(string data) { //var report = LoadExecutedQuery(Id); ReportDictionary report = Newtonsoft.Json.JsonConvert.DeserializeObject <ReportDictionary>(data); var sb = new StringBuilder(); var headers = ""; foreach (var item in report.ReportResult.Keys) { headers += item + ","; } sb.AppendLine(headers); //foreach (var item in report) //{ // sb.AppendLine(item.EmployeeId + "," + item.EmployeeName + "," + item.MonthsOfEmployment + ", " + item.EquipmentDeduction + "," + item.UniformDeduction + "," + item.TotalDeductions); //} return(File(new UTF8Encoding().GetBytes(sb.ToString()), "text/csv", "export.csv")); }
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); }
public ReportDictionary LoadExecutedQuery(int Id) { var SQL04 = "Data Source =OAKSQL04; Initial Catalog = customQuery; User Id=customQuery.service; Password=H6g12vxn92; Pooling=False; Connect Timeout = 30"; var SQL03 = "Data Source = OAKSQL03; Initial Catalog = Cameo_DWH; User Id = camarchive; Password = camarchive; Pooling = False; Connect Timeout = 30"; var loadQuery = @"SELECT sqlString FROM customQuery.dbo.userQueries WHERE Id = '" + Id + "'"; List <string> columns = new List <string>(); List <string> newColumns = new List <string>(); var loadedQuery = ""; List <ReportDictionary> f = new List <ReportDictionary>(); using (SqlConnection conn = new SqlConnection(SQL04)) { SqlCommand comm = new SqlCommand(loadQuery, conn); comm.Connection.Open(); comm.ExecuteNonQuery(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { loadedQuery = reader.GetString(0); } comm.Connection.Close(); } Dictionary <string, List <string> > loadedResults = new Dictionary <string, List <string> >(); var col = 0; // 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(SQL03)) { SqlCommand comm = new SqlCommand(loadedQuery, conn); comm.Connection.Open(); comm.ExecuteNonQuery(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { col += reader.VisibleFieldCount; for (var colArray = 0; colArray < reader.FieldCount; colArray++) { columns.Add(reader.GetName(colArray).ToString()); } } foreach (var s in columns) { if (!newColumns.Contains(s)) { newColumns.Add(s); } } foreach (var s in newColumns) { System.Diagnostics.Debug.WriteLine(s); } // add keys to the dictionary (column headers) for (var colArray = 0; colArray < newColumns.Count(); colArray++) { //List<string> list = new List<string>(); List <string> arraystring = new List <string>(); loadedResults.Add(newColumns[colArray], arraystring); } comm.Connection.Close(); } using (SqlConnection conn = new SqlConnection(SQL03)) { SqlCommand comm = new SqlCommand(loadedQuery, 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 < newColumns.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") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetString(h)); } else if (sqlType == "bigint") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetInt64(h).ToString()); } else if (sqlType == "float") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetFloat(h).ToString()); } else if (sqlType == "int") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetInt32(h).ToString()); } else if (sqlType == "decimal") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetDecimal(h).ToString()); } else if (sqlType == "numeric") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetDecimal(h).ToString()); } else if (sqlType == "datetime") { loadedResults[reader.GetName(h).ToString()].Add(reader.GetDateTime(h).ToString()); } } } comm.Connection.Close(); // pass the final dictionary object to the global dictionary //SQLString queryToSave = new SQLString(); //queryToSave.StoreSql(sqlSelect, queryName); // return a partial view to the JQuery request and pass in the dictionary class //return report; //foreach (var item in loadedResults) //{ // System.Diagnostics.Debug.WriteLine("this " + item.Value.Last().ToString()); //} } ReportDictionary report = new ReportDictionary(); report.ReportResult = loadedResults; return(report); }
public ActionResult DisplayLoadedReport(int Id) { ReportDictionary loadedQuery = LoadExecutedQuery(Id); return(PartialView("~/Views/Home/partialViews/_CompileSavedReport.cshtml", loadedQuery)); }
public ActionResult DisplayReport(UserCreatedQueryObject report, string username) { ReportDictionary query = RunFinalQuery(report, username); return(PartialView("~/Views/Home/partialViews/_CompileReport.cshtml", query)); }