public ActionResult EditProject(Guid id) { ProjectDonor pd = projectService.GetProjectDonorById(id); Models.Project project = new Models.Project(); project.Id = pd.Id.ToString(); project.EntityProject = pd.Project; project.ProjectNumber = pd.ProjectNumber; project.CurrencyId = pd.CurrencyId.ToString(); project.ProjectManagerId = pd.ProjectManagerId; project.DonorId = pd.DonorId.ToString(); project.StartDate = (DateTime)pd.StartDate; project.EndDate = (DateTime)pd.EndDate; project.OverrunAdjustment = pd.OverrunAdjustment; _donors = projectService.GetDonors(countryProg.Id); _currencies = projectService.GetCurrencies(countryProg.Id); project.StaffList = new SelectList(staffService.GetStaffByCountryProgramme(countryProg.Id), "StaffId", "StaffName"); project.Donors = new SelectList(_donors, "Id", "Name"); project.Currencies = new SelectList(_currencies, "Id", "Name", pd.CurrencyId); ViewBag.FormHeader = "Project: " + pd.ProjectNumber; ViewBag.ButtonText = Resources.BudgetController_String_SaveProject; ViewBag.EditMode = 1; ViewBag.Id = id; List <BudgetCategory> bcList = budgetService.GetBudgetCategories(pd); if (bcList.Count > 0) { ViewBag.HasBudget = 1; } else { ViewBag.HasBudget = 0; } return(View("CreateProject", project)); }
public ActionResult SaveCategory(Models.BudgetCategory bc) { ProjectDonor pd = projectService.GetProjectDonorById(bc.EntityBudgetCategory.ProjectDonorId.Value); BudgetCategory budgetCategory = budgetService.GetBudgetCategoryByNumber(bc.Number, pd.Id); if (bc.BudgetCategoryId == null || bc.BudgetCategoryId.Trim() == "") { if (budgetCategory == null) { budgetService.CreateBudgetCategory(bc.EntityBudgetCategory, pd); } else { ViewBag.ActionStatus = 0; ViewBag.StatusMsg = string.Format(Resources.BudgetController_String_CategoryExists, bc.EntityBudgetCategory.Number); } } else { if (budgetCategory == null) { budgetService.UpdateBudgetCategory(bc.EntityBudgetCategory); } else if (budgetCategory != null && budgetCategory.Id == bc.EntityBudgetCategory.Id) { budgetService.UpdateBudgetCategory(bc.EntityBudgetCategory); } else { ViewBag.ActionStatus = 0; ViewBag.StatusMsg = string.Format(Resources.BudgetController_String_CategoryExists, bc.EntityBudgetCategory.Number); } } return(CategoryList(pd.Id)); }
public List <ProjectBudget> GetBudgetLinesNotInBudget(ProjectDonor pd, BudgetCategory bc) { try { List <ProjectBudget> budgetLineList = new List <ProjectBudget>(); using (var context = new SCMSEntities()) { var budgetLines = context.ProjectBudgets.Where(b => b.BudgetCategory.ProjectDonorId == pd.Id).ToList <ProjectBudget>(); var subLines = context.ProjectBudgets.Where(s => s.BudgetCategoryId == bc.Id).ToList <ProjectBudget>(); foreach (var subLine in subLines) { if (!SubLineExistsInBudgetLines(subLine, budgetLines)) { budgetLineList.Add(subLine); } } } //sort bl budgetLineList = SCMS.Utils.BudgetLineSorter.SortBudgetLine(budgetLineList); return(budgetLineList); } catch (Exception ex) { return(new List <ProjectBudget>()); } }
public List <ProjectBudget> GetProjectBLByCategory(ProjectDonor pd, BudgetCategory bc) { try { List <ProjectBudget> projectBL = new List <ProjectBudget>(); using (var context = new SCMSEntities()) { var budgetLines = context.ProjectBudgets.Where(b => b.BudgetCategory.ProjectDonorId == pd.Id).ToList <ProjectBudget>(); var subLines = context.ProjectBudgets.Where(s => s.BudgetCategoryId == bc.Id).ToList <ProjectBudget>(); foreach (var budgetLine in budgetLines) { var curr = budgetLine.BudgetCategory.ProjectDonor.Currency; if (BudgetLineExistsInCategory(budgetLine, subLines)) { projectBL.Add(budgetLine); } } } projectBL = SCMS.Utils.BudgetLineSorter.SortBudgetLine(projectBL); return(projectBL); } catch (Exception ex) { return(new List <ProjectBudget>()); } }
public ActionResult LoadAddPPItemsToPO(Guid poId, Guid?pdId = null) { PurchaseOrder po = poService.GetPurchaseOrderById(poId); ProjectDonor pd = pdId.HasValue ? orService.GetProjectDonorById(pdId.Value) : orService.GetProjectNosWithPP().FirstOrDefault(); ProcurementPlan model = pd != null?ppService.GetProcurementPlanByProjectId(pd.Id) : null; if (model == null || !model.IsAuthorized) { model = new ProcurementPlan(); model.PPItemList = new List <ProcurementPlanItem>(); } else { model.PPItemList = model.ProcurementPlanItems.Where(p => (p.Quantity - p.ProcuredAmount) > 0).OrderBy(p => p.Item.Name).ToList(); foreach (ProcurementPlanItem ppItem in model.PPItemList) { ppItem.UnitCost = Math.Round(exchangeRateService.GetForeignCurrencyValue(po.CurrencyId, ppItem.CurrencyId, ppItem.UnitCost, countryProg.Id), 2); ppItem.QuantityToOrder = ppItem.Quantity - ppItem.ProcuredAmount; ppItem.TotalCost = Math.Round((ppItem.UnitCost * ppItem.QuantityToOrder), 2); } } model.PurchaseOrderId = poId; model.ProjectId = pd != null ? pd.ProjectId : Guid.Empty; model.Projects = new SelectList(orService.GetProjectsWithPP(), "Id", "Name", pd != null ? pd.ProjectId : Guid.Empty); model.ProjectDonors = new SelectList(orService.GetProjectNosWithPP(pd != null ? pd.ProjectId : Guid.Empty), "Id", "ProjectNumber", model.ProjectDonorId); return(View("AddPPItemsToPO", model)); }
public ActionResult ViewBudget(Guid id) { ProjectDonor pd = projectService.GetProjectDonorById(id); List <Models.Category> model = Models.BudgetExtension.PrepareBudgetModel(budgetService, exchangeRateService, currencyService, countryProg, pd, pd.Currency); return(RenderBudgetView(model, pd)); }
public List <ProjectBudget> GetBudgetLinesByGLCode(GeneralLedger gl, ProjectDonor pd) { using (var context = new SCMSEntities()) { var pbList = context.ProjectBudgets.Where(b => b.GeneralLedger.Id == gl.Id && b.BudgetCategory.ProjectDonorId == pd.Id).ToList(); pbList = SCMS.Utils.BudgetLineSorter.SortBudgetLine(pbList); return(pbList); } }
public ActionResult ApprovalSettings(Guid Id) { ProjectDonor pd = projectService.GetProjectDonorById(Id); List <Approver> approverList = staffService.GetProjectApprovers(Id); UserSession.CurrentSession.ProjectDonorId = Id; ViewBag.FormHeader = Resources.Global_String_Project + ": " + pd.ProjectNumber + " - " + Resources.Approver_Index_ApprovalSettings; ViewBag.Id = pd.Id; return(View("ApprovalSettings", approverList)); }
public ActionResult CreateCategory(Guid id) { ProjectDonor pd = projectService.GetProjectDonorById(id); budgetCategory = new Models.BudgetCategory(); budgetCategory.EntityBudgetCategory = new BudgetCategory(); budgetCategory.EntityBudgetCategory.ProjectDonorId = pd.Id; ViewBag.FormHeader = Resources.BudgetController_String_NewBudgetCategory; ViewBag.ButtonText = Resources.BudgetController_String_AddCategory; return(View(budgetCategory)); }
public ProjectDonor GetProjectDonorById(Guid projectDonorId) { using (var context = new SCMSEntities()) { ProjectDonor pd = (ProjectDonor)context.ProjectDonors.Where(p => p.Id == projectDonorId).FirstOrDefault(); Project pj = pd.Project; Donor d = pd.Donor; Currency c = pd.Currency; Person person = pd.Staff.Person; return pd; } }
public ActionResult Detail(ProjectDonor pdModel) { ProjectDonor pDonor = this.projectService.GetProjectDonorById(pdModel.Id); Currency displayCurrency = currencyService.GetCurrency((Guid)pdModel.CurrencyId); if (displayCurrency == null) displayCurrency = pDonor.Currency; List<Models.Category> categories = Models.BudgetExtension.PrepareBudgetModel(budSvc, exchangeRateService, currencyService, countryProg, pDonor, displayCurrency); Dictionary<String, String> data = new Dictionary<string, string>(); data.Add("{START-DATE}", pDonor.StartDate.ToString("dd/MM/yyyy")); data.Add("{END-DATE}", pDonor.EndDate.ToString("dd/MM/yyyy")); data.Add("{PROJECT}", pDonor.Project.Name); data.Add("{DONOR}", pDonor.Donor.Name); data.Add("{CURRENCY}", displayCurrency.ShortName); data.Add("{BUDGET_NUM}", pDonor.ProjectNumber); data.Add("{TOTAL_BUDGET}", categories[categories.Count - 1].TotalBudget.ToString("#,##0.00")); data.Add("{TOTAL_COMMITTED}", ND2S(categories[categories.Count - 1].TotalCommitted)); data.Add("{ACTUAL_POSTED}", ND2S(categories[categories.Count - 1].TotalPosted)); data.Add("{REMAINING_FUNDS}", ND2S(categories[categories.Count - 1].RemainingBalance)); data.Add("{GENERATED}", DateTime.Now.ToString("f")); String tbody = ""; for (int k = 0; k < categories.Count - 1; k++) { tbody += "<tr style=\"background-color: #EAF1DD; font-weight:bold;\"><td class='central'><b>" + categories[k].Number + "</b></td>"; tbody += "<td><b>" + categories[k].Name + "</b></td>"; tbody += "<td class='east'><b>" + categories[k].TotalBudget.ToString("#,##0.00") + "</b></td>"; tbody += "<td class='east'><b>" + ND2S(categories[k].TotalCommitted) + "</b></td>"; tbody += "<td class='east'><b>" + ND2S(categories[k].TotalPosted) + "</b></td>"; tbody += "<td class='east'><b>" + ND2S(categories[k].RemainingBalance) + "</b></td></tr>"; foreach (SCMS.UI.Models.BudgetLine b in categories[k].BudgetLines) { tbody += "<tr><td class='central'>" + b.LineNumber + "</td>"; tbody += "<td>" + b.Description + "</td>"; tbody += "<td class='east'>" + b.TotalBudget.ToString("#,##0.00") + "</td>"; tbody += "<td class='east'>" + ND2S(b.TotalCommitted) + "</td>"; tbody += "<td class='east'>" + ND2S(b.TotalPosted) + "</td>"; tbody += "<td class='east'>" + ND2S(b.RemainingBalance) + "</td></tr>"; } } data.Add("{DETAIL}", tbody); Byte[] output = WkHtml2Pdf.CreateReport(data, "project-budget.html");//= new Byte[1];;// return File(output, "application/pdf", "ProjectB_" + DateTime.Now.FormatDDMMMYYYYHHmm()); }
public ActionResult ViewBudgetInCurr(ProjectDonor pdModel) { ProjectDonor pd = projectService.GetProjectDonorById(pdModel.Id); Currency displayCurrency = currencyService.GetCurrency((Guid)pdModel.CurrencyId); if (displayCurrency == null) { displayCurrency = pd.Currency; } List <Models.Category> model = Models.BudgetExtension.PrepareBudgetModel(budgetService, exchangeRateService, currencyService, countryProg, pd, displayCurrency); return(RenderBudgetView(model, pd)); }
public List <ProjectBudget> GetProjectBugdetLines(ProjectDonor pd) { try { using (var context = new SCMSEntities()) { var pbList = context.ProjectBudgets.Where(pb => pb.BudgetCategory.ProjectDonorId == pd.Id).ToList <ProjectBudget>(); pbList = SCMS.Utils.BudgetLineSorter.SortBudgetLine(pbList); return(pbList); } } catch (Exception ex) { return(new List <ProjectBudget>()); } }
public List <BudgetCategory> GetBudgetCategories(ProjectDonor pd) { try { using (var context = new SCMSEntities()) { var list = context.BudgetCategories.Where(b => b.ProjectDonorId == pd.Id).OrderBy(b => b.Number).ToList <BudgetCategory>(); list = SCMS.Utils.BudgetLineSorter.SortCategory(list); return(list); } } catch (Exception ex) { return(new List <BudgetCategory>()); } }
public void DeleteBudgetLine(ProjectBudget budgetLine, ref ProjectDonor pd) { try { using (var context = new SCMSEntities()) { var pb = context.ProjectBudgets.First(b => b.Id == budgetLine.Id); var dummyCurrency = pd.Currency; var dummyDonor = pd.Donor; context.ProjectBudgets.Remove(pb); context.SaveChanges(); } } catch (Exception ex) { } }
private List <Models.MBCategoryLink> ConstructGLCodesList(ProjectDonor pd, List <MasterBudgetCategory> mbList) { Models.MBCategoryLink mbLink; List <Models.MBCategoryLink> mbLinkList = new List <Models.MBCategoryLink>(); foreach (MasterBudgetCategory mbc in mbList) { mbLink = new Models.MBCategoryLink(); mbLink.EntityMasterBudgetCateogry = mbc; mbLink.ProjectBudgetLines = PopulateMBCategoryBudgetLines(mbc, pd, mbList); if (mbLink.ProjectBudgetLines.Count > 0) { mbLinkList.Add(mbLink); } } return(mbLinkList); }
public ActionResult LinkToMB(Guid id) { ProjectDonor pd = projectService.GetProjectDonorById(id); List <MasterBudgetCategory> mbcList = masterBudgetService.GetMasterBudgetCategories(countryProg); List <GeneralLedger> glList = budgetService.GetGeneralLedgers(countryProg.Id); List <Models.Category> categories = ConstructCategoriesList(pd, glList); List <Models.MBCategoryLink> mbLinks = ConstructGLCodesList(pd, mbcList); Models.MasterBudgetLinker mbLinker = new Models.MasterBudgetLinker(); mbLinker.BudgetCaterogies = categories; mbLinker.MasterBudgetCategories = mbLinks; mbLinker.Id = pd.Id.ToString(); ViewBag.ProjectNo = pd.ProjectNumber; ViewBag.Currency = pd.Currency.ShortName; ViewBag.Donor = pd.Donor.Name; return(View("LinkToMB", mbLinker)); }
/// <summary> /// Used for display views with the SCMS.UI.Models.SubLine class /// </summary> /// <param name="bc"></param> /// <returns></returns> private List <Models.BudgetLine> GetCategorySubLines(BudgetCategory bc, ProjectDonor pd) { List <Models.BudgetLine> subLines = new List <Models.BudgetLine>(); List <ProjectBudget> bsl = budgetService.GetBudgetLinesNotInBudget(pd, bc); Models.BudgetLine subLine; foreach (ProjectBudget item in bsl) { subLine = new Models.BudgetLine(); subLine.EntityBudgetLine = item; subLine.SubLineId = item.Id; subLine.BudgetCategoryId = bc.Id.ToString(); subLine.BudgetCategoryNumber = bc.Number; subLines.Add(subLine); } return(subLines); }
private ActionResult RenderBudgetView(List <Models.Category> categories, ProjectDonor pd) { //If budget has no sublines, then we can't render the edit budget page. if (categories.Count == 0) { return(CategoryList(pd.Id)); } ViewBag.ProjectId = pd.Id.ToString(); ViewBag.ProjectNo = pd.ProjectNumber; ViewBag.StartDate = pd.StartDate.ToString("dd/MM/yyyy"); ViewBag.EndDate = pd.EndDate.ToString("dd/MM/yyyy"); ViewBag.Currency = pd.Currency.ShortName; ViewBag.Donor = pd.Donor.Name; ViewBag.DonorId = pd.Id; ViewBag.ProjectName = pd.Project.Name; ViewBag.ProjectManager = pd.Staff.Person.FirstName + " " + pd.Staff.Person.OtherNames; ViewBag.AllowedOverrun = pd.OverrunAdjustment.HasValue ? pd.OverrunAdjustment.Value.ToString("#,##0.00") + "%" : "NOT SET"; return(View("ViewBudget", categories)); }
private List <Models.Category> ConstructCategoriesList(ProjectDonor pd, List <GeneralLedger> glList) { List <BudgetCategory> bcList = budgetService.GetBudgetCategories(pd); Models.Category modelBC; List <Models.Category> categories = new List <Models.Category>(); foreach (BudgetCategory bc in bcList) { modelBC = new Models.Category(); modelBC.EntityBudgetCategory = bc; modelBC.Id = bc.Id.ToString(); modelBC.BudgetLines = PopulateCategoryBudgetLines(modelBC.EntityBudgetCategory, glList); if (modelBC.BudgetLines.Count > 0) { categories.Add(modelBC); } } return(categories); }
public ActionResult SaveLinkToMB(Models.MasterBudgetLinker mbl) { string[] x = Request.Form.GetValues("projectId"); ProjectDonor pd = projectService.GetProjectDonorById(new Guid(x[0])); if (mbl.BudgetCaterogies != null) { foreach (Models.Category cat in mbl.BudgetCaterogies) { foreach (Models.BudgetLine bl in cat.BudgetLines) { if (bl.GeneralLedgerId.HasValue) { budgetService.LinkBudgetLineToMasterBudget(bl.BudgetLineId, bl.GeneralLedgerId); } } } } ModelState.Clear(); return(LinkToMB(pd.Id)); }
public void AddBudgetLines(ProjectDonor pd, List <ProjectBudget> budget) { try { using (var context = new SCMSEntities()) { foreach (ProjectBudget pb in budget) { var newBgt = new ProjectBudget(); newBgt.Id = pb.Id; newBgt.TotalBudget = pb.TotalBudget; newBgt.TotalCommitted = newBgt.TotalPosted = 0; context.ProjectBudgets.Add(newBgt); } context.SaveChanges(); } } catch (Exception ex) { } }
public void CreateBudgetCategory(BudgetCategory budgetCategory, ProjectDonor pd) { try { using (var context = new SCMSEntities()) { var bc = new BudgetCategory(); bc.Id = Guid.NewGuid(); bc.Name = budgetCategory.Name; bc.Number = budgetCategory.Number; bc.Description = budgetCategory.Description; bc.ProjectDonorId = pd.Id; context.BudgetCategories.Add(bc); context.SaveChanges(); ClearProjectDonorList(); } } catch (Exception ex) { } }
/// <summary> /// Saves budget. It only affects the TotalBudget amount /// </summary> /// <param name="pd"></param> /// <param name="budget"></param> public void SaveBudgetLines(ProjectDonor pd, List <ProjectBudget> budgetLines) { try { using (var context = new SCMSEntities()) { foreach (var bl in budgetLines) { var budgetLine = context.ProjectBudgets.FirstOrDefault(b => b.Id == bl.Id); if (budgetLine == null) { continue; } budgetLine.TotalBudget = bl.TotalBudget; } context.SaveChanges(); ClearProjectBudgetList(); } } catch (Exception ex) { } }
public void CreateProjectDonor(Project project, DateTime startDate, DateTime endDate, Guid projectManagerId, string donorId, string currencyId, double? overrunAdjustment, ref string pdId) { if (ProjectNumberExits(project.ProjectNumber, null)) { pdId = null; return; } using (var context = new SCMSEntities()) { ProjectDonor pd = new ProjectDonor(); pd.Id = Guid.NewGuid(); pd.StartDate = startDate; pd.EndDate = endDate; pd.ProjectId = project.Id; pd.ProjectManagerId = projectManagerId; pd.DonorId = new Guid(donorId); pd.CurrencyId = new Guid(currencyId); pd.ProjectNumber = project.ProjectNumber; pd.OverrunAdjustment = overrunAdjustment; context.ProjectDonors.Add(pd); context.SaveChanges(); pdId = pd.Id.ToString(); } }
public ActionResult CategoryList(Guid id) { ProjectDonor pd = projectService.GetProjectDonorById(id); Models.BudgetCategory modelBC; decimal totalBudget; List <BudgetCategory> bcList = budgetService.GetBudgetCategories(pd); budgetCategories = new List <Models.BudgetCategory>(); foreach (BudgetCategory bc in bcList) { modelBC = new Models.BudgetCategory(); modelBC.EntityBudgetCategory = bc; modelBC.BudgetCategoryId = bc.Id.ToString(); modelBC.SubLines = PopulateCategorySubLines(modelBC.EntityBudgetCategory, out totalBudget); modelBC.TotalBudget = totalBudget; budgetCategories.Add(modelBC); } ViewBag.ProjectNo = pd.ProjectNumber; ViewBag.Donor = pd.Donor.Name; ViewBag.Currency = pd.Currency.ShortName; ViewBag.Id = pd.Id.ToString(); return(View("CategoryList", budgetCategories)); }
private List <Models.BudgetLine> PopulateMBCategoryBudgetLines(MasterBudgetCategory mbc, ProjectDonor pd, List <MasterBudgetCategory> mbcList) { List <Models.BudgetLine> blList = new List <Models.BudgetLine>(); Models.BudgetLine bl; List <ProjectBudget> budgetLines = budgetService.GetBudgetLinesByMBCategory(mbc, pd); foreach (ProjectBudget budgetLine in budgetLines) { bl = new Models.BudgetLine(); bl.BudgetCategoryId = mbc.Id.ToString(); bl.EntityBudgetLine = budgetLine; bl.GeneralLedgerCodes = new SelectList(mbcList, "Id", "DataText"); bl.BudgetLineId = budgetLine.Id; blList.Add(bl); } return(blList); }
public static List <Models.Category> PrepareBudgetModel(IBudgetService budgetService, IExchangeRateService exchangeRateService, ICurrencyService currencyService, CountryProgramme countryProg, ProjectDonor pd, Currency displayCurrency) { List <Model.BudgetCategory> bcList = budgetService.GetBudgetCategories(pd); List <Model.ProjectBudget> budgetLines; List <Models.Category> categories = new List <Models.Category>(); Models.Category category; int i = 0; foreach (Model.BudgetCategory bc in bcList) { budgetLines = budgetService.GetProjectBLByCategory(pd, bc); if (budgetLines.Count > 0) { category = new Models.Category(); category.EntityBudgetCategory = bc; category.Id = bc.Id.ToString(); if (i == 0) { category.ProjectDonorId = (Guid)bc.ProjectDonorId; category.Currencies = new SelectList(currencyService.GetCurrencies(countryProg.Id), "Id", "ShortName", displayCurrency.Id); category.CurrencyId = displayCurrency.Id; } AddCategoryLinesWithNumbers(exchangeRateService, category, budgetLines, displayCurrency, countryProg); categories.Add(category); } } if (categories.Count > 0) { ComputeTotals(categories); } return(categories); }
public List <ProjectBudget> GetBudgetLinesByMBCategory(MasterBudgetCategory mbc, ProjectDonor pd) { try { using (var context = new SCMSEntities()) { var pbList = context.ProjectBudgets.IncludeGeneralLedger().Where(b => b.GeneralLedger.MasterBudgetCategoryId == mbc.Id && b.BudgetCategory.ProjectDonorId == pd.Id).ToList(); pbList = SCMS.Utils.BudgetLineSorter.SortBudgetLine(pbList); return(pbList); } } catch (Exception ex) { return(new List <ProjectBudget>()); } }
public ActionResult Download(ProjectDonor pdModel) { ReportService rpSvc = new ReportService(); ProjectService pSvc = new ProjectService(); ProjectDonor pDonor = pSvc.GetProjectDonorById(pdModel.Id); Currency displayCurrency = currencyService.GetCurrency((Guid)pdModel.CurrencyId); if (displayCurrency == null) displayCurrency = pDonor.Currency; if (pDonor == null) { using (var stream = new MemoryStream()) { using (var xlPackage = new ExcelPackage()) { var ws = xlPackage.Workbook.Worksheets.Add("Sheet1"); ws.SetValue(1, 1, "Unknown Donor."); xlPackage.SaveAs(stream); } return File(stream.ToArray(), "application/excel", "unknown-donor" + " - " + DateTime.Now.Ticks.ToString() + ".xlsx"); } } using (var stream = new MemoryStream()) { using (var xlPackage = new ExcelPackage()) { var ws = xlPackage.Workbook.Worksheets.Add("Project Budget"); ws.SetValue(1, 1, "Project No."); ws.SetValue(1, 2, pDonor.Project.ProjectNumber); //format using (ExcelRange r = ws.Cells["A1:B1"]) { //r.Merge = true; r.Style.Font.SetFromFont(new Font("Arial", 10, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.Red); //r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93)); } ws.SetValue(2, 1, "Currency"); ws.SetValue(2, 2, displayCurrency.ShortName); //headings ws.SetValue(4, 1, "BUDGETLINE"); ws.SetValue(4, 2, "DESCRIPTION"); ws.SetValue(4, 3, "TOTAL BUDGET"); ws.SetValue(4, 4, "COMMITTED"); ws.SetValue(4, 5, "ACTUAL POSTING"); ws.SetValue(4, 6, "REMAINING FUND"); ws.SetValue(4, 7, "% SPEND"); // using (ExcelRange r = ws.Cells["A4:G4"]) { r.Style.Font.SetFromFont(new Font("Arial", 10, FontStyle.Regular)); //r.Style.Font.UnderLine = true; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; } //List<BudgetCategory> bCats = budSvc.GetBudgetCategories(pDonor); //List<Models.Category> categories = new List<Models.Category>(); //foreach (BudgetCategory b in bCats) //{ // List<SCMS.Model.ProjectBudget> pBudgets = budSvc.GetProjectBLByCategory(pDonor, b); // if (pBudgets.Count > 0) // { // Models.Category category = new Models.Category(); // category.EntityBudgetCategory = b; // category.Id = b.Id.ToString(); // AddCategoryLinesWithNumbers(category, pBudgets, displayCurrency); // categories.Add(category); // } //} List<Models.Category> categories = Models.BudgetExtension.PrepareBudgetModel(budSvc, exchangeRateService, currencyService, countryProg, pDonor, displayCurrency); int c = 6; foreach (var cat in categories) { if (cat.BudgetLines == null) continue; foreach (Models.BudgetLine line in cat.BudgetLines) { ws.SetValue(c, 1, line.LineNumber); ws.SetValue(c, 2, line.Description); ws.SetValue(c, 3, line.TotalBudget); ws.SetValue(c, 4, line.TotalCommitted); ws.SetValue(c, 5, line.TotalPosted); ws.SetValue(c, 6, line.RemainingBalance); ws.SetValue(c, 7, 100 * (line.TotalBudget > 0 ? (line.TotalPosted / line.TotalBudget) : 0)); c++; } ws.SetValue(c, 1, cat.Number); ws.SetValue(c, 2, cat.Name); ws.SetValue(c, 3, cat.TotalBudget); ws.SetValue(c, 4, cat.TotalCommitted); ws.SetValue(c, 5, cat.TotalPosted); ws.SetValue(c, 6, cat.RemainingBalance); ws.SetValue(c, 7, 100 * (cat.TotalBudget > 0 ? (cat.TotalPosted / cat.TotalBudget) : 0)); using (ExcelRange r = ws.Cells["A" + c.ToString() + ":G" + c.ToString()]) { r.Style.Font.Bold = true; } c++; } ws.Cells["C3:G" + c.ToString()].Style.Numberformat.Format = "#,##0.00"; ws.Cells["A1:G" + c.ToString()].AutoFitColumns(); xlPackage.SaveAs(stream); } return File(stream.ToArray(), "application/excel", pDonor.Project.ProjectNumber + " - " + DateTime.Now.Ticks.ToString() + ".xlsx"); } }