/// <summary>
/// Add a new entry to one of the dropdown tables in the database.
/// </summary>
/// <param name="table">Table to add a new entry to.</param>
/// <param name="entry">
/// DropdownEntry containing the value and code,
/// if exists.
/// </param>
public void addEntry(Constants.DropdownTable table,
DropdownEntry entry)
{
switch (table) {
case Constants.DropdownTable.Keyword:
var kw = new Keyword {
KeywordValue = entry.value
};
_db.Keywords.InsertOnSubmit(kw);
break;
case Constants.DropdownTable.QuestionType:
var qType = new QuestionType {
Code = entry.code,
Value = entry.value
};
_db.QuestionTypes.InsertOnSubmit(qType);
break;
case Constants.DropdownTable.Region:
var region = new Region {
Code = entry.code,
Value = entry.value
};
_db.Regions.InsertOnSubmit(region);
break;
case Constants.DropdownTable.RequestorType:
var rType = new RequestorType {
Code = entry.code,
Value = entry.value
};
_db.RequestorTypes.InsertOnSubmit(rType);
break;
case Constants.DropdownTable.TumourGroup:
var tGroup = new TumourGroup {
Code = entry.code,
Value = entry.value
};
_db.TumourGroups.InsertOnSubmit(tGroup);
break;
case Constants.DropdownTable.UserGroup:
var uGroup = new UserGroup {
Code = entry.code,
Value = entry.value
};
_db.UserGroups.InsertOnSubmit(uGroup);
break;
}
_db.SubmitChanges();
}
/// <summary>
/// Add entry to audit log table when an AuditType action is performed on a request.
/// </summary>
/// <param name="requestId">The ID of the Request</param>
/// <param name="userId">The ID of the User</param>
/// <param name="type">The type of audit action</param>
public void addEntry(long requestId, int userId,
Constants.AuditType type)
{
// Check if userID is valid
bool userFound = ((from up in _db.UserProfiles
where up.UserId == userId
select up.UserId).ToList()).Count != 0;
// Check if requestID is valid
bool idFound = (_db.Requests.Where(u => u.RequestID == requestId)
.Select(u => u.RequestID)).Count() != 0;
if (!userFound) {
throw new UserDoesNotExistException(userId);
}
if (!idFound) {
throw new RequestDoesNotExistException(requestId);
}
// Create a new entry in the log, using the Request ID, user ID, and action provided.
// Set to the current datetime
_db.AuditLogs.InsertOnSubmit(new AuditLog {
RequestID = requestId,
UserID = userId,
AuditType = (byte) type,
AuditDate = DateTime.Now
});
// Submit to DB.
_db.SubmitChanges();
}
public void TestGetInactiveEntries(Constants.DropdownTable table)
{
_table = table;
_ddc.createEntry(_table, _testCode, _testValue, false);
List<DropdownEntry> list = _ddc.getEntries(_table, false);
DropdownEntry toCheck = (from keys in list
where keys.value == _testValue
select keys).FirstOrDefault();
Assert.NotNull(toCheck);
Assert.AreEqual(toCheck.value.Trim(), _testValue);
Assert.False(toCheck.active);
}
/// <summary>
/// Create a new dropdown entry
/// </summary>
/// <param name="table">The table the entry is under</param>
/// <param name="code">The new code of the entry</param>
/// <param name="value">The new value of the entry</param>
/// <param name="active">The new status of the entry</param>
public void createEntry(Constants.DropdownTable table, string code,
string value, bool active)
{
switch (table) {
case Constants.DropdownTable.Keyword:
_db.Keywords.InsertOnSubmit(new Keyword {
KeywordValue = value,
Active = active
});
break;
case Constants.DropdownTable.QuestionType:
_db.QuestionTypes.InsertOnSubmit(new QuestionType {
Code = code,
Value = value,
Active = active
});
break;
case Constants.DropdownTable.Region:
_db.Regions.InsertOnSubmit(new Region {
Code = code,
Value = value,
Active = active
});
break;
case Constants.DropdownTable.RequestorType:
_db.RequestorTypes.InsertOnSubmit(new RequestorType {
Code = code,
Value = value,
Active = active
});
break;
case Constants.DropdownTable.TumourGroup:
_db.TumourGroups.InsertOnSubmit(new TumourGroup {
Code = code,
Value = value,
Active = active
});
break;
case Constants.DropdownTable.UserGroup:
_db.UserGroups.InsertOnSubmit(new UserGroup {
Code = code,
Value = value,
Active = active
});
break;
}
_db.SubmitChanges();
}
/// <summary>
/// Get all active dropdown entries from a specific table.
/// </summary>
/// <param name="table">Table containing the entries.</param>
/// <param name="activeOnly">True to check only active entries, False to check all</param>
/// <returns>List of dropdown table entries.</returns>
public List<DropdownEntry> getEntries(Constants.DropdownTable table,
bool activeOnly = true)
{
var list = new List<DropdownEntry>();
switch (table) {
case Constants.DropdownTable.Keyword:
List<Keyword> keywords = activeOnly
? _db.Keywords.Where(
kw => kw.Active).ToList()
: _db.Keywords.ToList();
list.AddRange(keywords.Select(kw =>
new KeywordEntry(kw.KeywordID,
kw
.KeywordValue,
kw.Active)));
break;
case Constants.DropdownTable.QuestionType:
List<QuestionType> qTypes = activeOnly
? _db.QuestionTypes.Where(
qType => qType.Active)
.ToList()
: _db.QuestionTypes.ToList();
list.AddRange(qTypes.Select(qType =>
new DropdownEntry(
qType.QuestionTypeID,
qType.Code,
qType.Value,
qType.Active)));
break;
case Constants.DropdownTable.Region:
List<Region> regions = activeOnly
? _db.Regions.Where(
region => region.Active)
.ToList()
: _db.Regions.ToList();
list.AddRange(regions.Select(region =>
new DropdownEntry(
region.RegionID,
region.Code,
region.Value,
region.Active)));
break;
case Constants.DropdownTable.RequestorType:
List<RequestorType> rTypes = activeOnly
? _db.RequestorTypes.Where(
rType => rType.Active)
.ToList()
: _db.RequestorTypes.ToList
();
list.AddRange(rTypes.Select(rType =>
new DropdownEntry(
rType.RequestorTypeID,
rType.Code,
rType.Value,
rType.Active)));
break;
case Constants.DropdownTable.TumourGroup:
List<TumourGroup> tGroups = activeOnly
? _db.TumourGroups.Where(
tGroup => tGroup.Active)
.ToList()
: _db.TumourGroups.ToList();
list.AddRange(tGroups.Select(tGroup =>
new DropdownEntry(
tGroup.TumourGroupID,
tGroup.Code,
tGroup.Value,
tGroup.Active)));
break;
case Constants.DropdownTable.UserGroup:
List<UserGroup> uGroups = activeOnly
? _db.UserGroups.Where(
uGroup => uGroup.Active)
.ToList()
: _db.UserGroups.ToList();
list.AddRange(uGroups.Select(uGroup =>
new DropdownEntry(
uGroup.GroupID,
uGroup.Code,
uGroup.Value,
uGroup.Active)));
break;
}
return list.OrderBy(dd => dd.code).ThenBy(dd => dd.value).ToList();
}
/// <summary>
/// Edit a the status of a dorpdown Entry.
/// </summary>
/// <param name="table">Dropdown table enum.</param>
/// <param name="id">Dropdown entry ID.</param>
/// <param name="active">True for active, false for inactive.</param>
public void editEntryStatus(Constants.DropdownTable table,
int id,
bool active)
{
try {
switch (table) {
case Constants.DropdownTable.Keyword:
Keyword keyword =
(from kw in _db.Keywords
where kw.KeywordID == id
select kw)
.First();
keyword.Active = active;
break;
case Constants.DropdownTable.QuestionType:
QuestionType qType =
(from qt in _db.QuestionTypes
where qt.QuestionTypeID == id
select qt)
.First();
qType.Active = active;
break;
case Constants.DropdownTable.Region:
Region region =
(from reg in _db.Regions
where reg.RegionID == id
select reg)
.First();
region.Active = active;
break;
case Constants.DropdownTable.RequestorType:
RequestorType rType =
(from rt in _db.RequestorTypes
where rt.RequestorTypeID == id
select rt)
.First();
rType.Active = active;
break;
case Constants.DropdownTable.TumourGroup:
TumourGroup tGroup =
(from tg in _db.TumourGroups
where tg.TumourGroupID == id
select tg)
.First();
tGroup.Active = active;
break;
case Constants.DropdownTable.UserGroup:
UserGroup uGroup =
(from ug in _db.UserGroups
where ug.GroupID == id
select ug)
.First();
uGroup.Active = active;
break;
}
_db.SubmitChanges();
} catch (InvalidOperationException) {
// No such entry
// TODO: Do something
}
}
/// <summary>
/// Edit an already-existing dropdown entry
/// </summary>
/// <param name="table">The table the entry is under</param>
/// <param name="id">The ID of the entry</param>
/// <param name="code">The new code of the entry</param>
/// <param name="value">The new value of the entry</param>
/// <param name="active">The new status of the entry</param>
public void editEntry(Constants.DropdownTable table, int id, string code,
string value, bool active)
{
switch (table) {
case Constants.DropdownTable.Keyword:
Keyword keyword =
_db.Keywords.First(kw => kw.KeywordID == id);
keyword.KeywordValue = value;
keyword.Active = active;
break;
case Constants.DropdownTable.QuestionType:
QuestionType qt =
_db.QuestionTypes.First(q => q.QuestionTypeID == id);
qt.Code = code;
qt.Value = value;
qt.Active = active;
break;
case Constants.DropdownTable.Region:
Region r = _db.Regions.First(reg => reg.RegionID == id);
r.Code = code;
r.Value = value;
r.Active = active;
break;
case Constants.DropdownTable.RequestorType:
RequestorType rt =
_db.RequestorTypes.First(
req => req.RequestorTypeID == id);
rt.Code = code;
rt.Value = value;
rt.Active = active;
break;
case Constants.DropdownTable.TumourGroup:
TumourGroup tg =
_db.TumourGroups.First(tum => tum.TumourGroupID == id);
tg.Code = code;
tg.Value = value;
tg.Active = active;
break;
case Constants.DropdownTable.UserGroup:
UserGroup ug = _db.UserGroups.First(usg => usg.GroupID == id);
ug.Code = code;
ug.Value = value;
ug.Active = active;
break;
}
_db.SubmitChanges();
}
/// <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 dictionary for the names of stratify groups to be used in the dataTable
/// </summary>
/// <param name="stratifyOption">Stratify option selected</param>
/// <returns>Returns the dictionary of subgroup codes for the stratify option selected</returns>
private Dictionary<int, string> getTypeNames(
Constants.StratifyOption stratifyOption)
{
Dictionary<int, string> codes = null;
switch (stratifyOption) {
case Constants.StratifyOption.Region:
codes = (from region in _db.Repository<Region>()
orderby region.Value
select region).ToDictionary(
region => region.RegionID, r => r.Code);
break;
case Constants.StratifyOption.RequestorType:
codes = (from callerType in _db.Repository<RequestorType>()
orderby callerType.Value
select callerType).ToDictionary(
ct => ct.RequestorTypeID, ct => ct.Code);
break;
case Constants.StratifyOption.TumorGroup:
codes = (from tumorGroup in _db.Repository<TumourGroup>()
orderby tumorGroup.Value
select tumorGroup).ToDictionary(
tg => tg.TumourGroupID, tg => tg.Code);
break;
}
return codes;
}
/// <summary>
/// Creates a dataTable using given parameters for generateMonthPerYearReport method
/// </summary>
/// <param name="startYear">Start year for the first column</param>
/// <param name="endYear">End year for the last column</param>
/// <param name="stratifyBy">StrafityBy option is passed on to get the stratifyBy Name</param>
/// <param name="dataType">DataType option passed on to get the cell daa</param>
/// <param name="dictionary">
/// Dictionary grouped by stratify groups for rows and
/// each stratify group sub-grouped into their years for columns
/// </param>
/// <returns>DataTable with proper data filled in.</returns>
private DataTable createDtForEachYear(int startYear, int endYear,
Constants.StratifyOption
stratifyBy,
Constants.DataType dataType,
Dictionary
<int,
Dictionary
<int, 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 year
for (int i = startYear; i <= endYear; i++) {
var yearColumn = new DataColumn(i.ToString(),
typeof (Int64)) {
DefaultValue = 0
};
dt.Columns.Add(yearColumn);
}
//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;
}
public ActionResult Index(
Constants.URLStatus status = Constants.URLStatus.None)
{
var db = new CAIRSDataContext();
var keywords = new Dictionary<long, List<string>>();
if (!User.IsInRole(Constants.Roles.VIEWER)) {
ViewBag.Requests = null;
return View();
}
// Select all from Requests
IQueryable<Request> requests;
// Create request list based on roles
if (User.IsInRole(Constants.Roles.ADMINISTRATOR)) {
requests = db.Requests.Select(r => r);
} else if (User.IsInRole(Constants.Roles.REQUEST_EDITOR)) {
requests = db.Requests.Select(r => r).Where(
r =>
(Constants.RequestStatus) r.RequestStatus !=
Constants.RequestStatus.Invalid).Where(
r => !db.RequestLocks
.Any(rl => rl.RequestID == r.RequestID &&
rl.UserProfile.UserName !=
User.Identity.Name));
} else {
requests = db.Requests.Select(r => r).Where(
r =>
(Constants.RequestStatus) r.RequestStatus ==
Constants.RequestStatus.Completed).Where(
r => !db.RequestLocks
.Any(rl => rl.RequestID == r.RequestID &&
rl.UserProfile.UserName !=
User.Identity.Name));
}
requests = requests.OrderBy(r => r.RequestStatus)
.ThenByDescending(r => r.TimeOpened).Take(10);
// Set the requests to null if there isn't anything on it,
// as the view doesn't seem to have Any() available.
if (!requests.Any()) {
requests = null;
}
// Grab keywords for the requests
if (requests != null) {
foreach (Request rq in requests) {
List<string> kw =
(from kws in db.Keywords
join kqs in db.KeywordQuestions on kws.KeywordID equals
kqs.KeywordID
where kqs.RequestID == rq.RequestID
select kws.KeywordValue).Distinct()
.ToList();
keywords.Add(rq.RequestID, kw);
}
}
ViewBag.Requests = requests;
ViewBag.Keywords = keywords;
if (status == Constants.URLStatus.Expired) {
ViewBag.Status =
"Your session has expired due to inactivity. All unsaved changes have been lost.";
ViewBag.StatusColor = "danger";
} else if (status == Constants.URLStatus.Unlocked) {
ViewBag.Status =
"The request has now been unlocked and is available for editing by all users.";
ViewBag.StatusColor = "success";
} else if (status == Constants.URLStatus.Deleted) {
ViewBag.Status =
"The request has been marked as invalid and cannot be seen by non-Administrators.";
ViewBag.StatusColor = "success";
} else if (status == Constants.URLStatus.AccessingLocked) {
ViewBag.Status =
"The request is locked and cannot be edited.";
ViewBag.StatusColor = "danger";
} else if (status == Constants.URLStatus.NotLockedToYou) {
ViewBag.Status =
"The request is not locked to you and cannot be edited.";
ViewBag.StatusColor = "danger";
} else if (status == Constants.URLStatus.SuccessfulEdit) {
ViewBag.Status =
"The request has been successfully edited.";
ViewBag.StatusColor = "success";
} else if (status == Constants.URLStatus.NoRequestEditorRole) {
ViewBag.Status =
"You no longer have permissions to create or edit requests.";
ViewBag.StatusColor = "danger";
} else if (status == Constants.URLStatus.SuccessfulCreate) {
ViewBag.Status =
"The request has been successfully created.";
ViewBag.StatusColor = "success";
} else if (status == Constants.URLStatus.EditingInvalid) {
ViewBag.Status =
"The request is marked as invalid and cannot be edited.";
ViewBag.StatusColor = "danger";
}
return View();
}
/// <summary>
/// Given a list of datatables, and the reportyType, it generates a excel file
/// using correct template and the data given
/// </summary>
/// <param name="reportType">Either Report or AuditLog from constants</param>
/// <param name="tableDictionary">
/// Dictionary of the tables to be exported. For either type,
/// the number of tables in the list must not exceed 15.
/// </param>
/// <param name="templatePath">Template file path</param>
/// <param name="workingCopyPath">Working copy path</param>
public void exportDataTable(Constants.ReportType reportType,
Dictionary<string, DataTable>
tableDictionary,
string templatePath, string workingCopyPath)
{
//Instead of creating a new excel file, let's use the template and make a copy to work with.
System.IO.File.Copy(templatePath, workingCopyPath, true);
if (tableDictionary.Count > 0) {
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(workingCopyPath, true)) {
WorkbookPart workbook = spreadsheet.WorkbookPart;
//Workbook workbook = spreadsheet.WorkbookPart.Workbook;
IEnumerable<WorksheetPart> sheets =
//workbook.Descendants<WorksheetPart>();
workbook.GetPartsOfType<WorksheetPart>();
DataTable table;
SheetData data;
string sheetName;
WorksheetPart worksheetPart;
//if there are more than 15 worksheets that needs to be created,
//the following method copies the first template and adds extra worksheets
//to the given workbook.
for (int x = 16; x <= tableDictionary.Count; x++) {
copySheet(spreadsheet.DocumentType, workbook, "Sheet1",
"Sheet" + x.ToString());
}
int i = 1;
foreach (var keyValue in tableDictionary) {
table = keyValue.Value;
sheetName = "Sheet" + (i).ToString();
worksheetPart = getWorksheetPart(workbook, sheetName);
//Merge Cells for title
string endColumnIndex =
getColumnName(table.Columns.Count);
MergeTwoCells(worksheetPart.Worksheet, "A1",
endColumnIndex + "1");
data =
worksheetPart.Worksheet.GetFirstChild<SheetData>
();
//creates title
var titleRow = new Row();
titleRow.RowIndex = (UInt32) 1;
Cell titleCell = createTextCell(1, 1, keyValue.Key);
titleRow.AppendChild(titleCell);
data.AppendChild(titleRow);
//add column names to the first row
var header = new Row();
header.RowIndex = (UInt32) Constants.REPORT_HEADER_ROW;
foreach (DataColumn column in table.Columns) {
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
Constants.REPORT_HEADER_ROW,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int j = 0; j < table.Rows.Count; j++) {
contentRow = table.Rows[j];
switch (reportType) {
case Constants.ReportType.Report:
data.AppendChild(
createContentRow(
Constants.CellDataType.Number,
contentRow,
j + Constants.DATA_START_ROW));
break;
default:
data.AppendChild(
createContentRow(
Constants.CellDataType.Text,
contentRow,
j + Constants.DATA_START_ROW));
break;
}
}
if (reportType == Constants.ReportType.Report) {
DrawingsPart drawingsPart =
worksheetPart.GetPartsOfType<DrawingsPart>()
.FirstOrDefault();
if (drawingsPart != null) {
ChartPart chartPart =
drawingsPart.GetPartsOfType<ChartPart>()
.FirstOrDefault();
fixChartData(chartPart, table.Rows.Count,
table.Columns.Count);
}
}
//incerement i to get the nextsheet
i++;
}
//if there were less than 15 worksheets to create, the rest of the templates are deleted.
for (int j = i; j < 16; j++) {
sheetName = "Sheet" + (j).ToString();
deleteAWorkSheet(workbook, sheetName);
}
}
}
string fileName = (reportType == Constants.ReportType.Report)
? "Reports.xlsx"
: "AuditLog.xlsx";
HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition",
"attachment; filename=" + fileName + ";");
response.TransmitFile(workingCopyPath);
response.Flush();
response.End();
if (System.IO.File.Exists(workingCopyPath)) {
System.IO.File.Delete(workingCopyPath);
}
}
/// <summary>
/// Creates a content row for the given dataRow
/// </summary>
/// <param name="cellType">The type of the Cell</param>
/// <param name="dataRow">DataRow that contains the data for the row it's creating</param>
/// <param name="rowIndex">Index for the row in the table</param>
/// <returns>Returns dataRow with proper data filled in</returns>
private Row createContentRow(Constants.CellDataType cellType,
DataRow dataRow,
int rowIndex)
{
var row = new Row {
RowIndex = (UInt32) rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++) {
Cell dataCell;
if (i > 0) {
switch (cellType) {
case Constants.CellDataType.Number:
dataCell = createNumCell(i + 1, rowIndex, dataRow[i]);
break;
default:
dataCell = createTextCell(i + 1, rowIndex,
dataRow[i]);
break;
}
} else {
dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
}
row.AppendChild(dataCell);
}
return row;
}
private void addEntryWithSpecifiedDateTimeHelper(Constants.AuditType at,
DateTime
randomizedDateTime)
{
var almc = new AuditLogManagementController();
// run method
almc.addEntry(rq.RequestID, up.UserId, at, randomizedDateTime);
// checks
AuditLog alCreated =
_dc.AuditLogs.FirstOrDefault(r => r.RequestID == rq.RequestID);
Assert.IsNotNull(alCreated, "No audit log created.");
Assert.IsTrue(alCreated.UserID == up.UserId,
"Audit Log created with wrong user ID.");
Assert.IsTrue(
Enum.GetName(typeof (Constants.AuditType), alCreated.AuditType)
.Equals(Enum.GetName(typeof (Constants.AuditType), at)),
"Audit Log created with wrong audit type.");
Assert.That(alCreated.AuditDate,
Is.EqualTo(randomizedDateTime).Within(1).Seconds,
"Audit Log created with wrong date.");
_dc.AuditLogs.DeleteOnSubmit(alCreated);
_dc.SubmitChanges();
}
private void addEntryHelper(Constants.AuditType at)
{
var almc = new AuditLogManagementController();
// run method
almc.addEntry(rq.RequestID, up.UserId, at);
//// checks
AuditLog alCreated =
_dc.AuditLogs.FirstOrDefault(r => r.RequestID == rq.RequestID);
Assert.IsNotNull(alCreated, "No audit log created.");
Assert.IsTrue(alCreated.UserID == up.UserId,
"Audit Log created with wrong user ID.");
Assert.IsTrue(
Enum.GetName(typeof (Constants.AuditType), alCreated.AuditType)
.Equals(Enum.GetName(typeof (Constants.AuditType), at)),
"Audit Log created with wrong audit type.");
Assert.IsTrue(
((DateTime.Now.Subtract(TimeSpan.FromSeconds(5))) <
alCreated.AuditDate),
"Audit Log created with incorrect date and time (too early).");
Assert.IsTrue(DateTime.Now > alCreated.AuditDate,
"Audit Log created with incorrect date and time (too late).");
_dc.AuditLogs.DeleteOnSubmit(alCreated);
_dc.SubmitChanges();
}
