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;
}
}
}
