I have a method that I use for generating a summary report, based on the options a user selects in pick lists, to count the number of a type of test that is completed in a region, with subset locations, within a date range.
Here's what the data looks like: Completed Test Counts by Location From 1 FEB 2015 to 1 MAR 2015
Total TestType Location Region Division
455 24 Hour Lab 1 City 1 Division A
28 24 Hour Lab 2 City 1 Division A
95 24 Hour Clinic Z City 2 Division A
189 24 Hour Clinic Y City 2 Division A
Here's what it might look like for a different test type, at the same time period:
Total TestType Location Region Division
285 48 Hour Lab 1 City 1 Division A
12 48 Hour Lab 2 City 1 Division A
75 48 Hour Clinic Z City 2 Division A
106 48 Hour Clinic Y City 2 Division A
Now, the users would like to see the summary breakdown by testType in one report (in SQL, adding another attribute to a group by). In my pick list of testTypeId, I am using 0 as my "All" item. In a perfect world, I'd have another addition to the IQueryable if testTypeId == 0, so that I can keep the same private method, rather than writing a new query.
Here's what we'd like the data to look like in the "ALL" situation:
Total TestType Location Region Division
455 24 Hour Lab 1 City 1 Division A
285 48 Hour Lab 1 City 1 Division A
59 12 Lead Lab 1 City 1 Division A
28 24 Hour Lab 2 City 1 Division A
12 48 Hour Lab 2 City 1 Division A
95 24 Hour Clinic Z City 2 Division A
75 48 Hour Clinic Z City 2 Division A
5 12 Lead Clinic Z City 2 Division A
189 24 Hour Clinic Y City 2 Division A
106 48 Hour Clinic Y City 2 Division A
8 12 Lead Clinic Y City 2 Division A
The example below shows what I have with what I'd like to add in it, but the syntax tells me I can't take an IQueryable containing an IGrouping and convert it to the target type of IQueryable.
Can anyone point me in a good direction here?
private CompletedCountReport GetCompletedCountsByRegion(int regionId, DateTime? startDate, DateTime? endDate, int testTypeId)
{
var ccRpt = new CompletedCountReport { CompletedCounts = new List<CompletedCount>(), StartDate = startDate, EndDate = endDate, SelectedRegionID = regionId};
var query = HolterTestDao.FindAll(new GetCompletedByRegionIdAndDates(regionId, startDate.Value, endDate.Value));
// a specific test type was selected
if (testTypeId > 0)
{
query = query.Where(x => x.HolterTestType == testTypeId);
}
// THIS IS WHAT I WANT TO ADD -> otherwise group by test type <- THIS IS WHAT I WANT TO ADD.
if (testTypeId == 0)
{
query = query.GroupBy(x => x.HolterTestType);
}
// order by locationID within the region.
query = query.OrderBy(x => x.Location.ID);
var htList = query.ToList();
// now Group by Location, to get counts.
var reportContents = htList.GroupBy(x => x.Location.ID);
foreach (var r in reportContents)
{
var rList = r.ToList();
var cc = new CompletedCount();
var loc = LocationDao.FindById(r.Key);
cc.Description = loc.Description;
cc.RegionId = loc.Region.ID;
cc.DivisionId = loc.Region.Division.ID;
cc.TestTypeId = testTypeId;
cc.Count = rList.Count;
ccRpt.CompletedCounts.Add(cc);
}
return ccRpt;
}
My solution was to OrderBy Location and HolterTestType, then GroupBy location for the basic contents, and then add another grouping in my loop through the results to get the counts by test type:
query = query.OrderBy(x => x.Location.ID).ThenBy(x => x.HolterTestType);
var htList = query.ToList();
// now Group by Location.
var reportContents = htList.GroupBy(x => x.Location.ID);
foreach (var r in reportContents)
{
//Group by TestType, to get counts.
var t = r.GroupBy(x => x.HolterTestType);
var tList = t.ToList();
foreach(var u in tList)
{
var cc = new CompletedCount();
var loc = LocationDao.FindById(r.Key);
cc.Description = loc.Description;
cc.RegionId = loc.Region.ID;
cc.DivisionId = loc.Region.Division.ID;
cc.TestTypeId = u.Key;
cc.Count = u.Count();
ccRpt.CompletedCounts.Add(cc);
}
}