void GetIndividualTablesAndHeadersForReport(IndividualTable it, string storedProcedureName_str, Dictionary <string, string> storedProcedureParameters_dict, TableValues tv_cl) { DateTime _Today = TimeZones.getCustomerTimeZones(1); SqlConnection conn = (new Database()).getConnection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = storedProcedureName_str; //cmd.CommandText = "TestReport"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; if (storedProcedureParameters_dict != null) { foreach (KeyValuePair <string, string> kvp in storedProcedureParameters_dict) { cmd.Parameters.AddWithValue("@" + kvp.Key, kvp.Value); } } // for unique key string uniqueKey_str = string.Empty; List <string> tableHeaders_list = new List <string>(); using (SqlDataReader _DataRootReader = cmd.ExecuteReader()) { bool moreResults_bool = true; while (moreResults_bool) { for (int i = 0; i < _DataRootReader.FieldCount; i++) { // get clear headers if (_DataRootReader.GetName(i).Contains("_HEADER")) { tableHeaders_list.Add(_DataRootReader.GetName(i)); } // get rows as headers on case there are several. when inside this loop else if (_DataRootReader.GetName(i).Contains("_RowAsHeader")) { while (_DataRootReader.Read()) { tableHeaders_list.Add(_DataRootReader[_DataRootReader.GetName(i)].ToString()); } } } moreResults_bool = _DataRootReader.NextResult(); } } Console.WriteLine(tableHeaders_list); Dictionary <string, Dictionary <string, string> > individualTables_dict = new Dictionary <string, Dictionary <string, string> >(); using (SqlDataReader _DataRootReader = cmd.ExecuteReader()) { bool moreResults_bool = true; while (moreResults_bool) { while (_DataRootReader.Read()) { // for each row we have unique id string uniqueId_str = string.Empty; string headerFromRow_str = string.Empty; for (int i = 0; i < _DataRootReader.FieldCount; i++) { // make sure if unique ID exists, it's always first in the SELECT // for each uniqiue ID create new row // TODO: We can also decide if we want to include this id in the table, tbh if (i == 0) { // if this one doesn't exists yet in the dictionary, then add it if (_DataRootReader.GetName(i).ToString().Contains("UniqueKey")) { // if the first column's name is UNIQUE KEY, we need to store it uniqueId_str = _DataRootReader.GetValue(i).ToString(); if (!individualTables_dict.ContainsKey(uniqueId_str)) { // if such record is not in the dictionary it, populate it with all the headers and default values Dictionary <string, string> headerValuesForIndivTable = new Dictionary <string, string>(); foreach (string _th_str in tableHeaders_list) { headerValuesForIndivTable.Add(_th_str, "---"); } individualTables_dict.Add(uniqueId_str, headerValuesForIndivTable); } } } // this is where we actually add values to the headers if (_DataRootReader.GetName(i).Contains("_HEADER")) { if (uniqueId_str != string.Empty) { if (individualTables_dict.ContainsKey(uniqueId_str)) { string value_str = _DataRootReader.GetValue(i).ToString(); // checking if it's empty or NULL if (value_str != string.Empty && value_str != null) { individualTables_dict[uniqueId_str][_DataRootReader.GetName(i).ToString()] = value_str; } } } } else if (_DataRootReader.GetName(i).Contains("_HeaderFromRow")) { headerFromRow_str = _DataRootReader.GetValue(i).ToString(); } else if (_DataRootReader.GetName(i).Contains("_RowAsValue")) { string value_str = _DataRootReader.GetValue(i).ToString(); // checking if it's empty if (value_str != string.Empty && value_str != null) { individualTables_dict[uniqueId_str][headerFromRow_str] = value_str; } } } } moreResults_bool = _DataRootReader.NextResult(); } } // saving it as List of INDIVIDUAL tables foreach (KeyValuePair <string, Dictionary <string, string> > kvp in individualTables_dict) { Dictionary <string, string> headerRow_dict = kvp.Value; List <string> oneRow_list = new List <string>(); foreach (KeyValuePair <string, string> hr in headerRow_dict) { oneRow_list.Add(hr.Value); } tv_cl.individualTables_list.Add(oneRow_list); } // cleaning the name foreach (string header_str in tableHeaders_list) { if (header_str.Contains("_HEADER")) { if (!header_str.Contains("UniqueKey")) { tv_cl.finalTableHeaders_list.Add(header_str.Replace("_HEADER", "")); } else { tv_cl.finalTableHeaders_list.Add(header_str.Replace("UniqueKey_HEADER_", "")); } } else { tv_cl.finalTableHeaders_list.Add(header_str); } } it.tableValueHeaders_list = tv_cl.finalTableHeaders_list; it.tableValues_list = tv_cl.individualTables_list; }
void GenerateCertainReport(string id) { // to get the stored procedure parameters var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string sql = String.Format(@"SELECT * FROM tblLevo2Reports WHERE jsonId='{0}'", id); // Get a json-object associated with an ID passed via URL string finalResult_str = string.Empty; using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Connection.Open(); SqlDataReader dt = cmd.ExecuteReader(); if (dt.HasRows) { while (dt.Read()) { finalResult_str = dt.GetString(1); } } } Console.WriteLine(finalResult_str); // Check if what was sent is a report of stored procedure parameter // dont forget to add parameters if (finalResult_str.Contains("iri_list") && finalResult_str.Contains("rp_cl")) { // since we can have several rgi_cl = JsonConvert.DeserializeObject <ReportGeneratorInfo>(finalResult_str); // save report name and its page size for rotativa for later TempData.Put("rotativaReportSizeType_str", rgi_cl.rp_cl.rotativaReportSizeType_str.ToString()); TempData.Put("rotativaReportName_str", rgi_cl.rp_cl.rotativaReportName_str); TempData.Put("rotativaReportPageOrientation_str", rgi_cl.rp_cl.rotativaReportPageOrientation_str); TempData.Put("footerHotelName_str", rgi_cl.rp_cl.footerHotelName_str); // there's only one header headerDisplayedOnEachPage_bool = rgi_cl.rp_cl.headerDisplayedOnEachPage_bool; Header reportHeader = new Header(); PopulateHeaderForCertainReport(reportHeader); ViewBag.ReportType = "Combined report"; report.header_cl = reportHeader; List <IndividualTable> tables_list = new List <IndividualTable>(); foreach (IndividualReportInfo iri_cl in rgi_cl.iri_list) { IndividualTable reportTable = new IndividualTable(); TableValues tv_cl = new TableValues(); // save stuff for splitting the table tv_cl.splitTableInSeveral_bool = iri_cl.shouldSplitTableIntoSeveral_bool; if (tv_cl.splitTableInSeveral_bool == true) { tv_cl.whenSplitWhichHeadersAreStatic_list = iri_cl.staticHeaders_list; } // save stuff for the summary tv_cl.summaryExists_bool = iri_cl.summaryExists_bool; if (tv_cl.summaryExists_bool == true) { if (iri_cl.summaryName_str != null) { tv_cl.summaryName_str = iri_cl.summaryName_str; } tv_cl.summaryValueHeaders_list = iri_cl.headersNotMentionedInSummaryIfExists_list; } PopulateTableCertainReport(reportTable, iri_cl.storedProcedureName_str, iri_cl.storedProcedureParameters_dict, tv_cl, iri_cl.tableName_str); tables_list.Add(reportTable); report.listOfTables_list = tables_list; // summary + split //CheckingForSummaryAndSplitting(); } //ViewBag.reportGot = JsonConvert.SerializeObject(report); } else if (finalResult_str.Contains("header_cl") && finalResult_str.Contains("listOfTables_list") && finalResult_str.Contains("rp_cl")) { report = JsonConvert.DeserializeObject <Report>(finalResult_str); TempData.Put("rotativaReportSizeType_str", report.rp_cl.rotativaReportSizeType_str.ToString()); TempData.Put("rotativaReportName_str", report.rp_cl.rotativaReportName_str); TempData.Put("rotativaReportPageOrientation_str", report.rp_cl.rotativaReportPageOrientation_str); TempData.Put("footerHotelName_str", report.rp_cl.footerHotelName_str); // there's only one header headerDisplayedOnEachPage_bool = report.rp_cl.headerDisplayedOnEachPage_bool; } // check all this stuff for all Individual tables at once CheckingForSummaryAndSplitting(); }
public void PopulateTableCertainReport(IndividualTable it, string storedProcedureName_str, Dictionary <string, string> storedProcedureParameters_dict, TableValues tv_cl, string tableName_str) { it.entityId_str = string.Empty; it.entityName_str = string.Empty; // if there's a table name if (tableName_str != null && tableName_str != string.Empty && tv_cl.tableNameWasDisplayed_bool == false) { it.tableName_str = tableName_str; tv_cl.tableNameWasDisplayed_bool = true; } else { it.tableName_str = string.Empty; } // talking to the real database GetIndividualTablesAndHeadersForReport(it, storedProcedureName_str, storedProcedureParameters_dict, tv_cl); // Putting in default values it.summaryExists_bool = tv_cl.summaryExists_bool; if (it.summaryExists_bool == true) { Dictionary <string, List <string> > summaryValueHeaders_dict = new Dictionary <string, List <string> > { // since we send names of headers that should not be in the summary, we remove them from the whole list of headers to get the ones that should be mentioned { tv_cl.summaryName_str, it.tableValueHeaders_list.Except(tv_cl.summaryValueHeaders_list).ToList() } }; it.summaryValueHeaders_dict = summaryValueHeaders_dict; it.summaryValues_list = new List <string>(); } // I would actually get several tables for table headers, values, summaries it.splitTableInSeveral_bool = tv_cl.splitTableInSeveral_bool; if (it.splitTableInSeveral_bool == true) { if (it.tableValueHeaders_list.Count > 10) { it.maxNumberOfRows_int = 8; ViewBag.cellWidthInPercentage = (100 / it.maxNumberOfRows_int).ToString() + "%"; it.whenSplitWhichHeadersAreStatic_list = tv_cl.whenSplitWhichHeadersAreStatic_list; ViewBag.staticHeaders_list = tv_cl.whenSplitWhichHeadersAreStatic_list; } else if (it.tableValueHeaders_list.Count > 5) { it.maxNumberOfRows_int = 5; ViewBag.cellWidthInPercentage = "20%"; it.whenSplitWhichHeadersAreStatic_list = tv_cl.whenSplitWhichHeadersAreStatic_list; ViewBag.staticHeaders_list = tv_cl.whenSplitWhichHeadersAreStatic_list; } // if for some weird reasons we wanted to collect enough data, but ended up with few - there's no need to split the table else { it.splitTableInSeveral_bool = false; } } }