/// <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(); }