public static DataTable SiteBySpeciesPivotTable(Guid speciesId, SortedDictionary <DateTime, int> combinedCount, int year)
{
DataTable resultTable = new DataTable("Site");
DataColumn locationName = new DataColumn("Site Name", typeof(string));
resultTable.Columns.Add(locationName);
resultTable.PrimaryKey = new DataColumn[] { locationName };
resultTable.Columns.Add("Grand Total", typeof(int));
resultTable.Columns.Add("Adjusted Count", typeof(string));
Dictionary <int, string> weekTranslation = new Dictionary <int, string>();
using (IDataReader reader = ResultsMapper.GetSiteBySpecies(speciesId, year))
{
while (reader.Read())
{
// Populate the table containing the list of sites
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("LocationName").ToString();
int count = reader.GetIntFromName("SpeciesCount");
DataRow row = null;
if (!resultTable.Rows.Contains(speciesName))
{
row = resultTable.NewRow();
row["Site 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;
// Populate the table (indirectly) that will drive the combined counts used in a histogram chart
DateTime weekDate = DateTime.Parse(week + "/10");
if (!combinedCount.ContainsKey(weekDate))
{
combinedCount.Add(weekDate, 0);
}
combinedCount[weekDate] += count;
}
}
// add the adjusted counts to the site listing
Collection <AdjustedCountBySite> adjcounts = ResultsMapper.GetAdjustedCounts(speciesId, year);
foreach (DataRow row in resultTable.Rows)
{
row["Adjusted Count"] = adjcounts.SingleOrDefault(x => x.SiteName.Equals(row["Site Name"])).AdjustedCount.ToString("0.000");
}
return(resultTable);
}
