Example #1
0
        public void New_DatesHaveNoValue()
        {
            var fiscalYear = new FiscalYear();

            Assert.False(fiscalYear.StartDate.HasValue);
            Assert.False(fiscalYear.EndDate.HasValue);
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }
Example #4
0
        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();
            }
        }
Example #5
0
        public void UnPostLedger(FiscalYear fy)
        {
            organization.LedgersDal.RemoveTransaction(fy.Id);
            fy.PostStatus = LedgerPostStatus.ReadyToPost;

            erpNodeDBContext.SaveChanges();
        }
Example #6
0
        public void UpdatePreviousFiscalYear(FiscalYear fiscalYear)
        {
            var previousFiscal = this.Find(fiscalYear.StartDate.AddDays(-1));

            fiscalYear.PreviousFiscal = previousFiscal;
            this.SaveChanges();
        }
Example #7
0
        public void Close(FiscalYear period)
        {
            this.CalculatePeriodAccountsBalance(period);
            period.Status = EnumFiscalYearStatus.Close;

            this.erpNodeDBContext.SaveChanges();
        }
Example #8
0
        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();
        }
Example #9
0
 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));
 }
Example #10
0
        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);
        }
Example #11
0
        /*
         * 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;
            }
        }
Example #12
0
        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));
        }
Example #13
0
        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 void New_With2DigitStartAndEnd_Assumes21Century()
        {
            var fiscalYear = new FiscalYear(start, end);

            Assert.AreEqual(2014, fiscalYear.StartDate.Value.Year);
            Assert.AreEqual(2015, fiscalYear.EndDate.Value.Year);
        }
Example #16
0
        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));
        }
Example #17
0
 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;
     }
 }
        public void New_DatesHaveNoValue()
        {
            var fiscalYear = new FiscalYear();

            Assert.False(fiscalYear.StartDate.HasValue);
            Assert.False(fiscalYear.EndDate.HasValue);
        }
Example #19
0
        // 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);
            }
        }
Example #20
0
        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);
        }
Example #21
0
        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();
        }
Example #22
0
        public void FiscalYearAddYear_Adding2Years_NewFiscalYear()
        {
            FiscalYear expected = new FiscalYear(2005, 2006);
            FiscalYear actual   = new FiscalYear(2003, 2004);

            Assert.AreNotEqual(expected, actual);
        }
Example #23
0
        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();
        }
Example #24
0
        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);
        }
Example #26
0
 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();
     }
 }
Example #27
0
        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);
        }
Example #28
0
 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();
 }
Example #30
0
        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);
        }
Example #31
0
 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);
 }
Example #33
0
        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;
        }