public void New_DatesHaveNoValue() { var fiscalYear = new FiscalYear(); Assert.False(fiscalYear.StartDate.HasValue); Assert.False(fiscalYear.EndDate.HasValue); }
public void New_With2DigitStartAndEnd_Assumes21Century() { var fiscalYear = new FiscalYear(start, end); Assert.AreEqual(2014, fiscalYear.StartDate.Value.Year); Assert.AreEqual(2015, fiscalYear.EndDate.Value.Year); }
public FiscalYear Create(DateTime date) { if (date < organization.DataItems.FirstDate) { return(null); } var firstDate = organization.DataItems.FirstDate; DateTime tagetFiscalFirstDate = new DateTime(date.Year, firstDate.Month, firstDate.Day); if (tagetFiscalFirstDate > date) { tagetFiscalFirstDate = new DateTime(date.Year - 1, firstDate.Month, firstDate.Day); } var fiscalYear = erpNodeDBContext.FiscalYears .Where(p => p.StartDate == tagetFiscalFirstDate.Date) .FirstOrDefault(); if (fiscalYear == null) { fiscalYear = new FiscalYear() { StartDate = tagetFiscalFirstDate, Status = EnumFiscalYearStatus.Open }; erpNodeDBContext.FiscalYears.Add(fiscalYear); erpNodeDBContext.SaveChanges(); } return(fiscalYear); }
public void UpdateFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear) { const string sqlText = @"UPDATE FiscalYear SET close_date = @close_date, open_date = @open_date, name = @name WHERE id = @id"; using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { if (fiscalYear.OpenDate != null) { command.AddParam("@open_date", ((DateTime)fiscalYear.OpenDate).Date); } else { command.AddParam("@open_date", null); } if (fiscalYear.CloseDate != null) { command.AddParam("@close_date", ((DateTime)fiscalYear.CloseDate).Date); } else { command.AddParam("@close_date", null); } command.AddParam("@name", fiscalYear.Name); command.AddParam("@id", fiscalYear.Id); command.ExecuteNonQuery(); } }
public void UnPostLedger(FiscalYear fy) { organization.LedgersDal.RemoveTransaction(fy.Id); fy.PostStatus = LedgerPostStatus.ReadyToPost; erpNodeDBContext.SaveChanges(); }
public void UpdatePreviousFiscalYear(FiscalYear fiscalYear) { var previousFiscal = this.Find(fiscalYear.StartDate.AddDays(-1)); fiscalYear.PreviousFiscal = previousFiscal; this.SaveChanges(); }
public void Close(FiscalYear period) { this.CalculatePeriodAccountsBalance(period); period.Status = EnumFiscalYearStatus.Close; this.erpNodeDBContext.SaveChanges(); }
private void UpdatePeriodBalances(FiscalYear fiscalYear) { var tempClosingBalances = erpNodeDBContext.Ledgers .Where(j => j.TransactionDate >= fiscalYear.StartDate && j.TransactionDate <= fiscalYear.EndDate) .Where(j => j.TransactionType != TransactionTypes.FiscalYearClosing && j.TransactionType != TransactionTypes.OpeningEntry) .GroupBy(o => o.AccountId) .Select(go => new TempClosingEntry { Id = Guid.NewGuid(), FiscalYearId = fiscalYear.Id, AccountGuid = go.Key, Account = go.FirstOrDefault().accountItem, Credit = go.Select(l => l.Credit) .DefaultIfEmpty(0) .Sum() ?? 0, Debit = go.Select(l => l.Debit) .DefaultIfEmpty(0) .Sum() ?? 0, }) .ToList(); foreach (var tempBalance in tempClosingBalances) { fiscalYear.UpdatePeriodBalance(tempBalance.Account, tempBalance.Debit, tempBalance.Credit); } fiscalYear.ClosingAccountsCalculateDateTime = DateTime.Today; erpNodeDBContext.SaveChanges(); }
public DbResult Update(FiscalYear obj, string flag) { SqlParameter[] param = { new SqlParameter("@flag", SqlDbType.VarChar, 50) { Value = flag == "i"? "i":"u" } , new SqlParameter("@user", SqlDbType.NVarChar, 128) { Value = SessionHelper.GetUserID() } , new SqlParameter("@id", SqlDbType.Int) { Value = obj.Id } , new SqlParameter("@ShortName", SqlDbType.NVarChar, 50) { Value = obj.ShortName } , new SqlParameter("@YearCode", SqlDbType.NVarChar, 50) { Value = obj.YearCode } , new SqlParameter("@StartDate", SqlDbType.DateTime) { Value = obj.StartDate } , new SqlParameter("@EndDate", SqlDbType.DateTime) { Value = obj.EndDate } }; return(SqlHelper.ParseDbResult("spFiscalYear", param)); }
public List <FiscalYear> List(bool Active = false) { List <FiscalYear> lst = new List <FiscalYear>(); SqlParameter[] param = { new SqlParameter("@flag", SqlDbType.VarChar, 50) { Value = Active == false ? "a" : "la" } }; DataTable result = SqlHelper.ExecuteDataTable("spFiscalYear", param); if (result != null && result.Rows.Count > 0) { foreach (DataRow drow in result.Rows) { FiscalYear obj = new FiscalYear(); obj.Id = Convert.ToInt32(drow["Id"]); obj.ShortName = drow["ShortName"].ToString(); obj.YearCode = drow["YearCode"].ToString(); obj.StartDate = Convert.ToDateTime(drow["StartDate"]); obj.EndDate = Convert.ToDateTime(drow["EndDate"].ToString()); lst.Add(obj); } } return(lst); }
/* * public DataTable getFiscalYear(SqlConnection con, int id) * { * DataTable dt = new DataTable(); * try * { * //SqlDataAdapter da = new SqlDataAdapter("Select * from ERP.dbo.FiscalYear Where (FiscalYearID=@FiscalYearID OR @FiscalYearID=0) AND (CompanyID=@cID) ORDER BY startdate DESC", con); * SqlDataAdapter da = new SqlDataAdapter("sploadFiscalYear", con); * da.SelectCommand.CommandType = CommandType.StoredProcedure; * da.SelectCommand.Parameters.Add("@FiscalYearID", SqlDbType.Int).Value = id; * da.SelectCommand.Parameters.Add("@cID", SqlDbType.Int).Value = LogInInfo.CompanyID; * da.Fill(dt); * da.Dispose(); * } * catch (Exception ex) * { * dt = null; * throw ex; * } * * return dt; * } */ public FiscalYear getAFiscalYear(SqlConnection con, int Fid) { FiscalYear fy = new FiscalYear(); DataTable dt = new DataTable(); try { SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM FiscalYear WHERE CompanyID=" + LogInInfo.CompanyID.ToString() + " AND FiscalYearID=" + Fid.ToString(), con); da.Fill(dt); da.Dispose(); if (dt.Rows.Count == 0) { return(null); } fy.FiscalYearID = GlobalFunctions.isNull(dt.Rows[0].Field <object>("FiscalYearID"), 0); fy.Titile = GlobalFunctions.isNull(dt.Rows[0].Field <object>("Title"), string.Empty); fy.StartDate = GlobalFunctions.isNull(dt.Rows[0].Field <object>("startdate"), new DateTime(1900, 1, 1)); fy.EndDate = GlobalFunctions.isNull(dt.Rows[0].Field <object>("enddate"), new DateTime(1900, 1, 1)); return(fy); } catch (Exception ex) { throw ex; } }
public IHttpActionResult PutFiscalYear(int id, FiscalYear fiscalYear) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (id != fiscalYear.FiscalYearId) { return(BadRequest()); } db.Entry(fiscalYear).State = EntityState.Modified; try { db.SaveChanges(); } catch (DbUpdateConcurrencyException) { if (!FiscalYearExists(id)) { return(NotFound()); } else { throw; } } return(StatusCode(HttpStatusCode.NoContent)); }
public async Task <ActionResult <FiscalYearVM> > CreateFiscalYear(FiscalYearVM fiscalyearVM) { try { if (fiscalyearVM == null) { return(BadRequest()); } // Add custom model validation error FiscalYear fiscalyear = await fiscalyearRepository.GetFiscalYearByname(fiscalyearVM.FiscalYear); if (fiscalyear != null) { ModelState.AddModelError("Name", $"FiscalYear name: {fiscalyearVM.FiscalYear.Name} already in use"); return(BadRequest(ModelState)); } await fiscalyearRepository.CreateFiscalYear(fiscalyearVM); return(CreatedAtAction(nameof(GetFiscalYear), new { id = fiscalyearVM.FiscalYear.Id }, fiscalyearVM)); } catch (DbUpdateException Ex) { return(StatusCode(StatusCodes.Status500InternalServerError, Ex.InnerException.Message)); } }
public async Task <IHttpActionResult> PutFiscalYear(int id, FiscalYear fiscalYear) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (id != fiscalYear.ID) { return(BadRequest()); } try { db.Entry(fiscalYear).State = EntityState.Modified; await db.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { if (!FiscalYearExists(id)) { return(NotFound()); } else { throw; } } return(CreatedAtRoute("DefaultApi", new { id = fiscalYear.ID }, fiscalYear)); }
public ActionResult Update(FiscalYear obj) { if (ModelState.IsValid) { try { string flag = "u"; if (obj.Id < 1) { flag = "i"; } var result = iFiscalYear.Update(obj, flag); return(Json(new { ErrorCode = result.ErrorCode, Message = result.Msg, Id = result.Id, JsonRequestBehavior.AllowGet })); } catch (Exception ex) { return(Json(new { ErrorCode = false, Message = ex.Message }, JsonRequestBehavior.AllowGet)); } } Response.TrySkipIisCustomErrors = true; string messages = string.Join("; ", ModelState.Values .SelectMany(x => x.Errors) .Select(x => x.ErrorMessage)); return(Json(new { ErrorCode = 1, Message = messages }, JsonRequestBehavior.AllowGet)); }
public void active(long fiscal_year_id) { try { using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) { FiscalYear fiscalYear = _fiscalYearRepo.getById(fiscal_year_id); if (fiscalYear == null) { throw new ItemNotFoundException($"Fiscal Year With Id {fiscal_year_id} is not Found."); } FiscalYear oldFiscalYear = _fiscalYearRepo.getByCurrent(); if (oldFiscalYear != null) { oldFiscalYear.is_current = false; _fiscalYearRepo.update(fiscalYear); } tx.Complete(); } } catch (Exception) { throw; } }
// Adds a new fiscal year to the system. public bool AddNewFiscalYear(FiscalYearViewModel fiscalyear) { try { var fiscalYearToBeSaved = new FiscalYear() { FiscalYearID = fiscalyear.FiscalYearID, FullNumericFiscalYear = fiscalyear.FullNumericFiscalYearMain, TextualFiscalYear = fiscalyear.TextualFiscalYearMain }; _context.FiscalYears.Add(fiscalYearToBeSaved); var response = _context.SaveChanges(); if (response > 0) { return(true); } else { return(false); } } catch (Exception) { return(false); } }
private static FiscalYear TweakYear(this FiscalYear fiscalYear) { var tweakedYear = A.Dummy <PositiveInteger>().ThatIs(y => y != fiscalYear.Year && y <= 9999); var result = new FiscalYear(tweakedYear); return(result); }
public void UpdateOpeningCOSG(FiscalYear fiscalYear) { //First Year, copy opening COSG if (fiscalYear.PreviousFiscal == null) { return; } var previusFiscalCOSG = fiscalYear.PreviousFiscal.PeriodItemsCOGS.ToList(); GetList(fiscalYear).ForEach(ItemCOSG => { var previusFiscalItemCOSG = previusFiscalCOSG .Where(prLine => prLine.ItemGuid == ItemCOSG.ItemGuid) .FirstOrDefault(); if (previusFiscalItemCOSG != null) { ItemCOSG.OpeningAmount = previusFiscalItemCOSG.RemainAmount; ItemCOSG.OpeningValue = previusFiscalItemCOSG.RemainValue; } }); SaveChanges(); }
public void FiscalYearAddYear_Adding2Years_NewFiscalYear() { FiscalYear expected = new FiscalYear(2005, 2006); FiscalYear actual = new FiscalYear(2003, 2004); Assert.AreNotEqual(expected, actual); }
public void ClearCOGS(FiscalYear fiscalYear) { var periodCost = fiscalYear.PeriodItemsCOGS.ToList(); periodCost.ForEach(pc => { if (pc.PostStatus == LedgerPostStatus.Posted) { UnPostLedger(pc); } erpNodeDBContext.PeriodItemsCOGS.Remove(pc); }); this.SaveChanges(); organization.Items.GetInventories.ToList() .ForEach(item => { var periodItemCOGS = new PeriodItemCOGS() { Id = Guid.NewGuid(), FiscalYear = fiscalYear, ItemGuid = item.Id, LastCalculateDate = DateTime.Today }; fiscalYear.PeriodItemsCOGS.Add(periodItemCOGS); }); this.SaveChanges(); }
public static T[] CalculateTotals <T>(IEnumerable <T> items, T target, FiscalYear year) where T : ProfileDataModel { var totals = new T[4]; totals[0] = Sum(items) ?? (T)Activator.CreateInstance(target.GetType()); totals[0].ProfileName = "Total Actual Borrowing"; totals[1] = CloneEmpty(target); if (totals[1] != null) { totals[1].ConsolidatedAmount = target.ConsolidatedAmount; totals[1].OefcAmount = target.OefcAmount; totals[1].ProvinceAmount = target.ProvinceAmount; totals[1].ProfileName = "Borrowing Requirement"; } totals[2] = CloneEmpty(target); if (totals[2] != null) { totals[2].ConsolidatedAmount = totals[1].ConsolidatedAmount - totals[0].ConsolidatedAmount; totals[2].OefcAmount = totals[1].OefcAmount - totals[0].OefcAmount; totals[2].ProvinceAmount = totals[1].ProvinceAmount - totals[0].ProvinceAmount; totals[2].ProfileName = "Remaining"; } totals[3] = CalculateBorrowingPace(target, totals[0], year); return(totals); }
public IHttpActionResult PutFiscalYear(int id, FiscalYear fiscalYear) { if (!ModelState.IsValid) { return BadRequest(ModelState); } if (id != fiscalYear.FiscalYearId) { return BadRequest(); } db.Entry(fiscalYear).State = EntityState.Modified; try { db.SaveChanges(); } catch (DbUpdateConcurrencyException) { if (!FiscalYearExists(id)) { return NotFound(); } else { throw; } } return StatusCode(HttpStatusCode.NoContent); }
public static string InsUpdDelFiscalYear(char Event, FiscalYear obj, out int returnId) { returnId = 0; try { var cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "USP_IUD_FiscalYearSetup"; cmd.Connection = DL_CCommon.ConnectionForCommonDb(); cmd.Parameters.AddWithValue("@EVENT", Event); cmd.Parameters.AddWithValue("@Id", obj.Id); cmd.Parameters.AddWithValue("@StartDate", obj.StartDate); cmd.Parameters.AddWithValue("@EndDate", obj.EndDate); cmd.Parameters.AddWithValue("@Iscurrent", obj.IsCurrent); var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200); outparameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(outparameter); var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100); outId.Direction = ParameterDirection.Output; cmd.Parameters.Add(outId); cmd.ExecuteNonQuery(); var msg = cmd.Parameters[outparameter.ParameterName].Value; returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value); return(Convert.ToString(msg)); } catch (Exception ex) { throw new ArgumentException(ex.Message); } finally { DL_CCommon.ConnectionForCommonDb().Close(); } }
public void InsertUpdateDelete() { FiscalYearController fiscalYearController = new FiscalYearController(); //create new entity FiscalYear fiscalYear = new FiscalYear(); fiscalYear.fiscalYearId = Guid.NewGuid(); fiscalYear.fiscalYear = DateTime.Now; fiscalYear.startDate = DateTime.Now; fiscalYear.endDate = DateTime.Now; fiscalYear.entryDate = DateTime.Now; fiscalYear.appUserId = Guid.NewGuid(); fiscalYear.modifiedDate = DateTime.Now; fiscalYear.remark = "Test Remark"; //insert var result1 = fiscalYearController.Post(fiscalYear); //update var result2 = fiscalYearController.Post(fiscalYear); //delete var result3 = fiscalYearController.Delete(fiscalYear.fiscalYearId); //assert Assert.IsNotNull(result1); Assert.IsNotNull(result2); Assert.IsNotNull(result3); Assert.IsTrue(result1 is OkResult); Assert.IsTrue(result2 is OkResult); Assert.IsTrue(result3 is OkResult); }
int IAdministrationService.SaveCompany(Company Company, FiscalYear fy) { try { //foreach (var item in Context.Companies) //{ // item.IsActive = false; //} //Company.IsActive = true; var v = Context.FiscalYears.Where(a => a.StartDate == fy.StartDate).Where(a => a.EndDate == fy.EndDate).FirstOrDefault(); if (v == null) { Context.FiscalYears.Add(fy); Context.SaveChanges(); Company.FiscalYearId = fy.FiscalYearId; } if (Company.CompanyId <= 0) { Context.Companies.Add(Company); } else { Company.FiscalYearId = v.FiscalYearId; Context.Entry(Company).State = EntityState.Modified; } return(Context.SaveChanges()); } catch (Exception ex) { throw ex; } }
public void LoadDdls() { ddlFiscalYear.DataSource = FiscalYear.GetDSLAFiscalYears(); ddlFiscalYear.DataValueField = "pvalue"; ddlFiscalYear.DataTextField = "ptext"; ddlFiscalYear.DataBind(); }
public void FiscalYear_TwoUnequalFY_False() { FiscalYear f1 = new FiscalYear(2000, 2001); FiscalYear f2 = new FiscalYear(2015, 2016); Assert.IsFalse(f1.Equals(f2)); Assert.IsFalse(f1 == f2); }
public void FiscalYear_ParseFromInalidString_ThrowArgumentException() { Assert.ThrowsException <ArgumentException>(() => FiscalYear.Parse("2000-200")); Assert.ThrowsException <ArgumentException>(() => FiscalYear.Parse("2000-2004")); Assert.ThrowsException <ArgumentException>(() => FiscalYear.Parse("2002-2000")); Assert.ThrowsException <ArgumentException>(() => FiscalYear.Parse("2001- 2000")); Assert.ThrowsException <ArgumentException>(() => FiscalYear.Parse("2001- 2000")); }
public void Remove(FiscalYear fiscalYear) { if (Context.Entry(fiscalYear).State == EntityState.Detached) { context.FiscalYears.Attach(fiscalYear); } context.FiscalYears.Remove(fiscalYear); }
public void FiscalYear_TwoEqualFY_True() { FiscalYear f1 = new FiscalYear(2000, 2001); FiscalYear f2 = new FiscalYear(2000, 2001); Assert.IsTrue(f1.Equals(f2)); Assert.IsTrue(f1 == f2); }
public void FiscalYear_Starts_July1Midnight() { var fiscalYear = new FiscalYear(start, end); var startDate = fiscalYear.StartDate.Value; Assert.AreEqual(7, startDate.Month); Assert.AreEqual(1, startDate.Day); Assert.AreEqual(0, startDate.Hour); Assert.AreEqual(0, startDate.Minute); Assert.AreEqual(0, startDate.Second); }
public void FiscalYear_Ends_BeforeJuly1Midnight() { var fiscalYear = new FiscalYear(start, end); var endDate = fiscalYear.EndDate.Value; Assert.AreEqual(6, endDate.Month); Assert.AreEqual(30, endDate.Day); Assert.AreEqual(11, endDate.Hour); Assert.AreEqual(59, endDate.Minute); Assert.AreEqual(59, endDate.Second); }
public IHttpActionResult PostFiscalYear(FiscalYear fiscalYear) { if (!ModelState.IsValid) { return BadRequest(ModelState); } db.FiscalYears.Add(fiscalYear); db.SaveChanges(); return CreatedAtRoute("DefaultApi", new { id = fiscalYear.FiscalYearId }, fiscalYear); }
/// <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; }
/// <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; }