public static DataTable SpeciesSitePivotTable(Guid speciesId, int year)
{
DataTable resultTable = new DataTable();
DataColumn locationName = new DataColumn("Species Name", typeof(string));
resultTable.Columns.Add(locationName);
resultTable.PrimaryKey = new DataColumn[] { locationName };
resultTable.Columns.Add("Grand Total", typeof(int));
Dictionary <int, string> weekTranslation = new Dictionary <int, string>();
using (IDataReader reader = ResultsMapper.GetSpeciesBySite(speciesId, year))
{
while (reader.Read())
{
int weekNumber = reader.GetIntFromName("Week");
string week = string.Empty;
if (weekTranslation.ContainsKey(weekNumber))
{
week = weekTranslation[weekNumber];
}
else
{
week = Conversion.GetDateForWeekNumber(weekNumber);
}
if (!resultTable.Columns.Contains(week))
{
resultTable.Columns.Add(week, typeof(string));
}
string speciesName = reader.GetValueFromName("CommonName").ToString();
int count = reader.GetIntFromName("SpeciesCount");
DataRow row = null;
if (!resultTable.Rows.Contains(speciesName))
{
row = resultTable.NewRow();
row["Species Name"] = speciesName;
resultTable.Rows.Add(row);
}
else
{
row = resultTable.Rows.Find(speciesName);
}
row[week] = count.ToString();
int currentTotal = 0;
if (!int.TryParse(row["Grand Total"].ToString(), out currentTotal))
{
currentTotal = 0;
}
row["Grand Total"] = currentTotal + count;
}
}
return(resultTable);
}
