/// <summary>
/// Creates list of dataTables for monthly report, to be exported based on the years and criteria specified
/// </summary>
/// <param name="startYear">Start year selected by user</param>
/// <param name="endYear">End year selected by 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>List of DataTables for each data type chosen</returns>
public Dictionary<string, DataTable> generateYearlyReport(int startYear,
int endYear,
IEnumerable
<
Constants.
DataType>
dataToDisplay,
Constants.
StratifyOption
stratifyBy)
{
var dataTablesForReport = new Dictionary<string, DataTable>();
var startDate = new DateTime(startYear, 4, 1, 0, 0, 0);
var enDated = new DateTime(endYear + 1, 3,
DateTime.DaysInMonth(endYear, 3), 23, 59,
59);
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 <=
enDated
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<FiscalYear, List<Request>>>
regionAndYear =
regionDictionary.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new FiscalYear(r.TimeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key,
grp => grp.ToList()));
//creates dataTable for each data and adds it to the list of dataTables
foreach (Constants.DataType dataType in dataToDisplay) {
int titleIndex = ((int) stratifyBy - 1)*4 +
(int) dataType;
dataTablesForReport.Add(
Constants.DATATABLE_TITLES[titleIndex],
createDtForEachFiscalYear(startYear, endYear,
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 <=
enDated
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<FiscalYear, List<Request>>>
callerAndYear =
callerDictionary.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new FiscalYear(r.TimeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key,
grp => grp.ToList()));
//creates dataTable for each data and adds it to the list of dataTables
foreach (Constants.DataType dataType in dataToDisplay) {
int titleIndex = ((int) stratifyBy - 1)*4 +
(int) dataType;
dataTablesForReport.Add(
Constants.DATATABLE_TITLES[titleIndex],
createDtForEachFiscalYear(startYear, endYear,
stratifyBy, dataType,
callerAndYear));
}
break;
case Constants.StratifyOption.TumorGroup:
Dictionary<int, List<QandRwithTimestamp>> qrTumourGrpDic =
(from reqs in _db.Repository<Request>()
where
reqs.TimeOpened > startDate &&
reqs.TimeOpened <= enDated
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());
Dictionary
<int, Dictionary<FiscalYear, List<QandRwithTimestamp>>>
tgAndYear =
qrTumourGrpDic.ToDictionary(
keyValuePair => keyValuePair.Key,
keyValuePair =>
keyValuePair.Value.GroupBy(
r => new FiscalYear(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],
createDtForEachFiscalYear(startYear, endYear,
stratifyBy, dataType,
tgAndYear));
}
break;
default:
//Retrieves the requests from the database which opened within the given timeFrame
//then group them by the year
Dictionary<FiscalYear, List<Request>> dictionaryByYear = (from
reqs
in
_db
.Repository
<
Request
>
()
where
reqs
.TimeOpened >
startDate &&
reqs
.TimeOpened <=
enDated
select
reqs)
.ToList().GroupBy(r => new FiscalYear(r.TimeOpened))
.Select(grp => grp)
.ToDictionary(grp => grp.Key, grp => grp.ToList());
//Dictionary<FiscalYear, List<Request>> dictionaryByYear = (from reqs in _db.Requests
// where
// reqs.TimeOpened > start &&
// reqs.TimeOpened <= end
// group reqs by new FiscalYear(reqs.TimeOpened)
// into listByYear
// select listByYear).ToDictionary(r => r.Key,
// 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 totalNumOfYears = endYear - startYear + 1;
var startFiscalYear = new FiscalYear(startYear);
for (int i = 0; i < totalNumOfYears; i++) {
var monthColumn =
new DataColumn(startFiscalYear.ToString(),
typeof (Int64)) {
DefaultValue = 0
};
dt.Columns.Add(monthColumn);
startFiscalYear.addYear(1);
}
foreach (
Constants.DataType dataType in
dataToDisplay.OrderByDescending(x => x).Reverse()) {
//adds a row for each dataType in the table
DataRow newRow = dt.NewRow();
switch (dataType) {
case Constants.DataType.AvgTimePerRequest:
newRow[Constants.DataTypeStrings.DATA_TYPE] =
Constants.DataTypeStrings.AVG_TIME;
foreach (var keyValue in dictionaryByYear) {
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 dictionaryByYear) {
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 dictionaryByYear) {
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 dictionaryByYear) {
newRow[keyValue.Key.ToString()] =
totalTimeSpent(keyValue.Value);
}
break;
}
dt.Rows.Add(newRow);
}
dataTablesForReport.Add(Constants.DATATABLE_TITLES[0], dt);
break;
}
return dataTablesForReport;
}
/// <summary>
/// Creates a dataTable fore each fiscal year for given stratify grouping
/// </summary>
/// <param name="startYear">Report start year</param>
/// <param name="endYear">Report end year</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 fiscal year</param>
/// <returns>DataTable fore each fiscal year for given stratify grouping</returns>
private DataTable createDtForEachFiscalYear(int startYear, int endYear,
Constants.StratifyOption
stratifyBy,
Constants.DataType dataType,
Dictionary
<int,
Dictionary
<FiscalYear,
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 = endYear - startYear + 1;
var startFiscalYear = new FiscalYear(startYear);
for (int i = 0; i < totalNumOfMonths; i++) {
var monthColumn = new DataColumn(startFiscalYear.ToString(),
typeof (Int64)) {
DefaultValue = 0
};
dt.Columns.Add(monthColumn);
startFiscalYear.addYear(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);
}
//adds a row for each startify groups with perper data filled in
foreach (
var keyValuePair in
dictionary.OrderByDescending(x => x.Key).Reverse()) {
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;
}
