/// <summary>
/// Creates a DataTable fore each fiscal year for given stratify grouping
/// </summary>
/// <param name="startTime">Report start date</param>
/// <param name="endTime">Report end date</param>
/// <param name="stratifyBy">StratifyOption to retireve group names</param>
/// <param name="dataType">Data to be displayed</param>
/// <param name="dictionary">QandRwithTimestamp grouped into stratify group then in to their month</param>
/// <returns>DataTable for each month for given stratify grouping</returns>
private DataTable createDtForEachMonth(DateTime startTime,
DateTime endTime,
Constants.StratifyOption
stratifyBy,
Constants.DataType dataType,
Dictionary
<int,
Dictionary
<MonthYearPair,
List<QandRwithTimestamp>>>
dictionary)
{
var dt = new DataTable();
dt.Clear();
//Finds the string representation of stratifyBy option and create a column
string stratifyGroups =
Enum.GetName(typeof (Constants.StratifyOption), stratifyBy);
var stratifyGrpColum = new DataColumn(stratifyGroups,
typeof (string));
dt.Columns.Add(stratifyGrpColum);
//create column for each month
int totalNumOfMonths = endTime.Month +
(endTime.Year - startTime.Year)*12 -
startTime.Month;
var startMonthYearPair = new MonthYearPair(startTime.Month,
startTime.Year);
for (int i = 0; i < totalNumOfMonths; i++) {
var monthColumn = new DataColumn(startMonthYearPair.ToString(),
typeof (Int64)) {
DefaultValue = 0
};
dt.Columns.Add(monthColumn);
startMonthYearPair.addmonth(1);
}
//gets the names of the stratify groups. ie, callerType,region or tumourGroup Codes
Dictionary<int, string> idToName = getTypeNames(stratifyBy);
//Even if the dictionary does not contain the no group data, table should still show them.
if (!dictionary.ContainsKey(-1)) {
DataRow noGroupRow = dt.NewRow();
noGroupRow[stratifyGroups] = "No " + stratifyGroups;
dt.Rows.Add(noGroupRow);
}
foreach (
var keyValuePair in
dictionary.OrderByDescending(x => x.Key).Reverse()) {
//adds a row for each stratify groups in the table
DataRow newRow = dt.NewRow();
//if the key is null then it should create a row for 'No group assigned' requests
if (keyValuePair.Key != -1) {
newRow[stratifyGroups] = idToName[keyValuePair.Key];
idToName.Remove(keyValuePair.Key);
} else {
newRow[stratifyGroups] = "No " + stratifyGroups;
}
//now visit each year which the requests are sub-grouped by year
//and adds the proper value for the cell in the dataTable
foreach (var valuePair in keyValuePair.Value) {
switch (dataType) {
case Constants.DataType.AvgTimePerRequest:
newRow[valuePair.Key.ToString()] =
averageTime(valuePair.Value);
break;
case Constants.DataType.AvgTimeToComplete:
newRow[valuePair.Key.ToString()] =
avgTimeFromStartToComplete(valuePair.Value);
break;
case Constants.DataType.TotalNumOfRequests:
newRow[valuePair.Key.ToString()] =
valuePair.Value.Count;
break;
case Constants.DataType.TotalTimeSpent:
newRow[valuePair.Key.ToString()] =
totalTimeSpent(valuePair.Value);
break;
}
}
dt.Rows.Add(newRow);
}
DataRow groupRow;
foreach (var group in idToName) {
groupRow = dt.NewRow();
groupRow[stratifyGroups] = group.Value;
dt.Rows.Add(groupRow);
}
return dt;
}
/// <summary>
/// Creates list of dataTables for monthly report, to be exported based on the month and criteria specified
/// </summary>
/// <param name="startDate">Start date, selected by the user</param>
/// <param name="endDate">End date, selected by the user</param>
/// <param name="dataToDisplay">Date Types to represent, selected by the user</param>
/// <param name="stratifyBy">Stratify option, selected by the user</param>
/// <returns>The list of data tables, one table for each data type chosen</returns>
public Dictionary<string, DataTable> generateMonthlyReport(
DateTime startDate, DateTime endDate,
IEnumerable<Constants.DataType> dataToDisplay,
Constants.StratifyOption stratifyBy)
{
var dataTablesForReport = new Dictionary<string, DataTable>();
//executes different methods depending on the stratify options selected
switch (stratifyBy) {
case Constants.StratifyOption.Region:
//Retrieves the requests from the database which opened within the given timeFrame
//then group them by the region
Dictionary<int, List<Request>> regionDictionary = (from reqs
in
_db
.Repository
<
Request
>()
where
reqs
.TimeOpened >
startDate &&
reqs
.TimeOpened <=
endDate
group
reqs
by
reqs
.RegionID
into
regionGroups
select
regionGroups)
.ToDictionary(r => nullableToInt(r.Key),
r =>
r.ToList());
//Sub-groups the regionGroups by the year the request is opened.
Dictionary<int, Dictionary<MonthYearPair, List<Request>>>
regionAndYear =
regionDictionary.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new MonthYearPair(r.TimeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key,
grp => grp.ToList()));
//creates dataTable for each data and adds it to the dictionary of dataTables
foreach (Constants.DataType dataType in dataToDisplay) {
int titleIndex = ((int) stratifyBy - 1)*4 +
(int) dataType;
dataTablesForReport.Add(
Constants.DATATABLE_TITLES[titleIndex],
createDtForEachMonth(startDate, endDate, stratifyBy,
dataType, regionAndYear));
}
break;
case Constants.StratifyOption.RequestorType:
//Retrieves the requests from the database which opened within the given timeFrame
//then group them by the callerType
Dictionary<int, List<Request>> callerDictionary = (from reqs
in
_db
.Repository
<
Request
>()
where
reqs
.TimeOpened >
startDate &&
reqs
.TimeOpened <=
endDate
group
reqs
by
reqs
.RequestorTypeID
into
callerGroups
select
callerGroups)
.ToDictionary(r => nullableToInt(r.Key),
r =>
r.ToList());
//Sub-groups the regionGroups by the year the request is opened.
Dictionary<int, Dictionary<MonthYearPair, List<Request>>>
callerAndYear =
callerDictionary.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new MonthYearPair(r.TimeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key,
grp => grp.ToList()));
//creates dataTable for each data and adds it to the dictionary of dataTables
foreach (Constants.DataType dataType in dataToDisplay) {
int titleIndex = ((int) stratifyBy - 1)*4 +
(int) dataType;
dataTablesForReport.Add(
Constants.DATATABLE_TITLES[titleIndex],
createDtForEachMonth(startDate, endDate, stratifyBy,
dataType, callerAndYear));
}
break;
case Constants.StratifyOption.TumorGroup:
//Retrieves the QuestionResponse from the database which opened within the given timeFrame,
//adds the open, close timestamps, then group them by the tumourGroup
Dictionary<int, List<QandRwithTimestamp>> qrTumourGrpDic =
(from reqs in _db.Repository<Request>()
where
reqs.TimeOpened > startDate &&
reqs.TimeOpened <= endDate
join qr in _db.Repository<QuestionResponse>() on
reqs.RequestID
equals qr.RequestID
select
new QandRwithTimestamp(qr, reqs.TimeOpened,
reqs.TimeClosed)).ToList()
.GroupBy(
q =>
q.qr
.TumourGroupID)
.Select(
grp =>
grp)
.ToDictionary
(grp => nullableToInt(grp.Key), grp => grp.ToList());
//Sub-groups the regionGroups by the year the question(request) is opened.
Dictionary
<int,
Dictionary<MonthYearPair, List<QandRwithTimestamp>>>
tgAndYear =
qrTumourGrpDic.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new MonthYearPair(r.timeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key,
grp => grp.ToList()));
//creates dataTable for each data and adds it to the dictionary of dataTables
foreach (Constants.DataType dataType in dataToDisplay) {
int titleIndex = ((int) stratifyBy - 1)*4 +
(int) dataType;
dataTablesForReport.Add(
Constants.DATATABLE_TITLES[titleIndex],
createDtForEachMonth(startDate, endDate, stratifyBy,
dataType, tgAndYear));
}
break;
default:
//Retrieves the requests from the database which opened within the given timeFrame
//then group them by the year
Dictionary<MonthYearPair, List<Request>> dictionaryByMonth = (from
reqs
in
_db
.Repository
<
Request
>
()
where
reqs
.TimeOpened >
startDate &&
reqs
.TimeOpened <=
endDate
group
reqs
by
new {
reqs
.TimeOpened
.Month,
reqs
.TimeOpened
.Year
}
into
listByYear
select
listByYear)
.ToDictionary(
r => new MonthYearPair(r.Key.Month, r.Key.Year),
r =>
r.ToList());
var dt = new DataTable();
dt.Clear();
//Finds the string representation of stratifyBy option and create a column
var dataTypeColumn =
new DataColumn(Constants.DataTypeStrings.DATA_TYPE,
typeof (string));
dt.Columns.Add(dataTypeColumn);
//create column for each month
int totalNumOfMonths = endDate.Month +
(endDate.Year - startDate.Year)*12 -
startDate.Month;
var startMonthYearPair = new MonthYearPair(startDate.Month,
startDate.Year);
for (int i = 0; i < totalNumOfMonths; i++) {
var monthColumn =
new DataColumn(startMonthYearPair.ToString(),
typeof (Int64)) {
DefaultValue = 0
};
dt.Columns.Add(monthColumn);
startMonthYearPair.addmonth(1);
}
foreach (
Constants.DataType dataType in
dataToDisplay.OrderByDescending(x => x).Reverse()) {
//adds a row for each dataType in the table
DataRow newRow = dt.NewRow();
//depending what data we need, the following enters the correct value for the the data cell.
switch (dataType) {
case Constants.DataType.AvgTimePerRequest:
newRow[Constants.DataTypeStrings.DATA_TYPE] =
Constants.DataTypeStrings.AVG_TIME;
foreach (var keyValue in dictionaryByMonth) {
newRow[keyValue.Key.ToString()] =
averageTime(keyValue.Value);
}
break;
case Constants.DataType.AvgTimeToComplete:
newRow[Constants.DataTypeStrings.DATA_TYPE] =
Constants.DataTypeStrings
.AVG_TIME_TO_COMPLETE;
foreach (var keyValue in dictionaryByMonth) {
newRow[keyValue.Key.ToString()] =
avgTimeFromStartToComplete(
keyValue.Value);
}
break;
case Constants.DataType.TotalNumOfRequests:
newRow[Constants.DataTypeStrings.DATA_TYPE] =
Constants.DataTypeStrings.TOTAL_NUM;
foreach (var keyValue in dictionaryByMonth) {
newRow[keyValue.Key.ToString()] =
keyValue.Value.Count;
}
break;
case Constants.DataType.TotalTimeSpent:
newRow[Constants.DataTypeStrings.DATA_TYPE] =
Constants.DataTypeStrings.TOTAL_TIME_SPENT;
foreach (var keyValue in dictionaryByMonth) {
newRow[keyValue.Key.ToString()] =
totalTimeSpent(keyValue.Value);
}
break;
}
dt.Rows.Add(newRow);
}
dataTablesForReport.Add(Constants.DATATABLE_TITLES[0], dt);
break;
}
return dataTablesForReport;
}
