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