private static DataSet TransformTablesForCamperSummaryByState(DataSet dsIn)
{
const string CampName = "CampName";
DataSet dsOut = new DataSet();
DataTable dtAllSelectedCamps = dsIn.Tables[0];
DataTable dtRaw;
for (int i = 1; i < dsIn.Tables.Count; i++)
{
DataTable dt = dsIn.Tables[i].Clone();
dtRaw = dsIn.Tables[i];
long CampID;
DataRow[] tmprows;
foreach (DataRow dr in dtAllSelectedCamps.Rows)
{
CampID = (long)dr[0];
DataRow newrow = dt.NewRow();
newrow[CampName] = dr[1].ToString();
tmprows = dtRaw.Select("CampID = " + CampID.ToString());
for (int j = 2; j < dtRaw.Columns.Count; j++)
{
if (tmprows.Length == 1)
{
newrow[j] = tmprows[0][j];
}
else
{
newrow[j] = 0;
}
}
dt.Rows.Add(newrow);
}
dt.Columns.Remove("CampID");
dsOut.Tables.Add(dt);
}
return(dsOut);
}
/// <summary>
/// Make all the tables have the same number of rows (camps)
/// Calculate the appropriate columns (Days in camp)
/// </summary>
/// <param name="dsIn"></param>
/// <returns></returns>
private static DataSet TransformTablesForCamperSummaryReportByCamp(DataSet dsIn)
{
const string CampName = "Camp Name";
const string NumberOfCampers = "# of Campers";
const string DayRangeOne = "12-17 days";
const string DayRangeTwo = "18 days";
const string DayRangeThree = "19+ days";
DataSet dsOut = new DataSet();
DataTable dtAllSelectedCamps = dsIn.Tables[0];
DataTable dtRaw, dtCount;
for (int i = 1; i < dsIn.Tables.Count; i += 2)
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn(CampName, System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn(NumberOfCampers, System.Type.GetType("System.Int32")));
dt.Columns.Add(new DataColumn(DayRangeOne, System.Type.GetType("System.Int32"))); // need this for the correct column order in excel report
dt.Columns.Add(new DataColumn(DayRangeTwo, System.Type.GetType("System.Int32")));
dt.Columns.Add(new DataColumn(DayRangeThree, System.Type.GetType("System.Int32")));
dtRaw = dsIn.Tables[i];
dtCount = dsIn.Tables[i + 1];
long CampID;
foreach (DataRow dr in dtAllSelectedCamps.Rows)
{
CampID = (long)dr[0];
DataRow newrow = dt.NewRow();
newrow[CampName] = dr[1].ToString();
DataRow[] tmprows = dtCount.Select("CampID = " + CampID.ToString());
if (tmprows.Length > 0)
{
newrow[NumberOfCampers] = tmprows[0]["TotalCount"];
}
else
{
newrow[NumberOfCampers] = 0;
}
// set all other columns to zero so we can have calculation below
newrow[DayRangeOne] = 0;
newrow[DayRangeTwo] = 0;
newrow[DayRangeThree] = 0;
if ((int)newrow[NumberOfCampers] > 0)
{
tmprows = dtRaw.Select("CampID = " + CampID);
if (tmprows.Length >= 0)
{
for (int j = 0; j < tmprows.Length; j += 2)
{
DateTime startDate = TransformIntoDateTime(tmprows[j]["Answer"].ToString());
// we need to add one more day because TimeSpan would not consider the last day
DateTime endDate = TransformIntoDateTime(tmprows[j + 1]["Answer"].ToString()).AddDays(1);
TimeSpan ts = endDate - startDate;
if (ts.Days == 18)
{
newrow[DayRangeTwo] = (int)newrow[DayRangeTwo] + 1;
}
else if (ts.Days > 18)
{
newrow[DayRangeThree] = (int)newrow[DayRangeThree] + 1;
}
else if (ts.Days >= 12)
{
newrow[DayRangeOne] = (int)newrow[DayRangeOne] + 1;
}
}
newrow[NumberOfCampers] = (int)newrow[DayRangeTwo] + (int)newrow[DayRangeThree] + (int)newrow[DayRangeOne];
}
}
dt.Rows.Add(newrow);
}
dsOut.Tables.Add(dt);
}
return(dsOut);
}