public static Boolean ExpenseExist(String expense) { BudgetDB db = new BudgetDB(); var ExpenseTitle = (from list in db.uacs where list.Title == expense select list).ToList(); if (ExpenseTitle.Count > 0) { return(true); } return(false); }
public void CreateExcel(String date_from, String date_to) { BudgetDB db = new BudgetDB(); FileInfo excelFile = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("~/excel_reports/SAOB_NEW.xlsx")); try { FileInfo tempExcel = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("~/excel_reports/SAOB_NEW2.xlsx")); tempExcel.Delete(); excelFile.CopyTo(System.Web.HttpContext.Current.Server.MapPath("~/excel_reports/SAOB_NEW2.xlsx")); } catch { excelFile.CopyTo(System.Web.HttpContext.Current.Server.MapPath("~/excel_reports/SAOB_NEW2.xlsx")); } FileInfo newFile = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("~/excel_reports/SAOB_NEW2.xlsx")); new SaobExcelSheet1().CreateExcel(db, newFile, date_from, date_to); }
public void CreateExcel(BudgetDB db, FileInfo newFile, string DateFrom, string DateTo) { DateTime date1 = Convert.ToDateTime(DateFrom); DateTime date2 = Convert.ToDateTime(DateTo); ExcelPackage pck = new ExcelPackage(newFile); ExcelWorksheet worksheet = pck.Workbook.Worksheets[2]; Int32 startRow = 6; Double grand_total = 0; worksheet.Cells[3, 1].Value = "As of " + date2.ToString("MMMM dd, yyyy"); var allotments = db.allotments.Where(p => p.year == GlobalData.Year && p.active == 1).OrderBy(p => p.Code2).ToList(); Double allotment_total = 0; foreach (Allotments _allotments in allotments) { allotment_total = 0; //_thead.AddCell(new PdfPCell(new Paragraph(_allotments.Title.ToUpper(), new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0, Colspan = 7 }); // DISPLAY ALLOTMENT TITLE worksheet.Cells[startRow, 1].Style.Font.Name = "TAHOMA"; worksheet.Cells[startRow, 1].Style.Font.Size = 10; worksheet.Cells[startRow, 1].Style.Font.Bold = true; worksheet.Cells[startRow, 1].Value = _allotments.Title.ToUpper(); startRow++; var fsh = db.fsh.Where(p => p.allotment == _allotments.ID.ToString() && p.type == "REG" && p.active == 1).ToList(); Double fundsource_total = 0; foreach (FundSourceHdr _fsh in fsh) { fundsource_total = 0; //_thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); //_thead.AddCell(new PdfPCell(new Paragraph(_fsh.SourceTitle.ToUpper().ToString(), new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); // DISPLAY FUNDSOURCE TITLES worksheet.Cells[startRow, 2].Style.Font.Name = "TAHOMA"; worksheet.Cells[startRow, 2].Style.Font.Size = 10; worksheet.Cells[startRow, 2].Value = _fsh.SourceTitle.ToUpper(); var fsa = (from list in db.fsa join expensecode in db.uacs on list.expense_title equals expensecode.Title where list.fundsource == _fsh.ID.ToString() select new { Amount = list.amount } ).ToList(); Double fsa_total_amount = 0; foreach (var _fsa in fsa) { fsa_total_amount += _fsa.Amount; } fundsource_total += fsa_total_amount; allotment_total += fundsource_total; //DISPLAY FUNDSOURCE ALLOTMENT & AMOUNT worksheet.Cells[startRow, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[startRow, 3].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells[startRow, 3].Value = fsa_total_amount; // var realignments = (from ) startRow++; } /* * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("SUBTOTAL", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph(fundsource_total > 0 ? fundsource_total.ToString("N", new CultureInfo("en-US")) : "", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); */ Double saa_allotment_total = 0; var _sub_allotments = db.fsh.Where(p => p.allotment == _allotments.ID.ToString() && p.type == "SUB" && p.active == 1).ToList(); if (_sub_allotments.Count > 0) { saa_allotment_total = 0; //_thead.AddCell(new PdfPCell(new Paragraph(_allotments.Code.ToUpper().ToString() + " SUB-ALLOTMENT", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0, Colspan = 7 }); worksheet.Cells[startRow, 1].Style.Font.Name = "TAHOMA"; worksheet.Cells[startRow, 1].Style.Font.Size = 10; worksheet.Cells[startRow, 1].Style.Font.Bold = true; worksheet.Cells[startRow, 1].Value = _allotments.Code.ToUpper() + " SUB-ALLOTMENT"; startRow++; Double saa_fund_source_total = 0; foreach (FundSourceHdr _fsh_saa in _sub_allotments) { saa_fund_source_total = 0; //_thead.AddCell(new PdfPCell(new Paragraph(_fsh_saa.SourceTitle.ToString(), new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); //_thead.AddCell(new PdfPCell(new Paragraph(_fsh_saa.desc.ToString(), new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); // DISPLAY SUB-ALLOTMENT FUNDSOURCE worksheet.Cells[startRow, 1].Style.Font.Name = "TAHOMA"; worksheet.Cells[startRow, 1].Style.Font.Size = 10; worksheet.Cells[startRow, 1].Style.Font.Bold = true; worksheet.Cells[startRow, 1].Value = _fsh_saa.SourceTitle.ToString(); worksheet.Cells[startRow, 2].Style.Font.Name = "TAHOMA"; worksheet.Cells[startRow, 2].Style.Font.Size = 10; worksheet.Cells[startRow, 2].Value = _fsh_saa.desc.ToString(); var saa_amt = (from list in db.fsa join expensecode in db.uacs on list.expense_title equals expensecode.Title where list.fundsource == _fsh_saa.ID.ToString() select new { Amount = list.amount } ).ToList(); Double saa_fsa_amt_total = 0; foreach (var _saa_amt in saa_amt) { saa_fsa_amt_total += _saa_amt.Amount; } saa_fund_source_total += saa_fsa_amt_total; allotment_total += saa_fund_source_total; worksheet.Cells[startRow, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[startRow, 3].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells[startRow, 3].Value = saa_fsa_amt_total; /* * _thead.AddCell(new PdfPCell(new Paragraph(saa_fsa_amt_total > 0 ? saa_fsa_amt_total.ToString("N", new CultureInfo("en-US")) : "", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_LEFT, Border = 0 }); */ startRow++; } /* * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("SUBTOTAL", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph(saa_fund_source_total > 0 ? saa_fund_source_total.ToString("N", new CultureInfo("en-US")) : "", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); */ } /* * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 0 }); * _thead.AddCell(new PdfPCell(new Paragraph("TOTAL " + _allotments.Code.ToString(), new Font(Font.FontFamily.HELVETICA, 6f, Font.BOLD))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph(allotment_total > 0 ? allotment_total.ToString("N", new CultureInfo("en-US")) : "", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); * _thead.AddCell(new PdfPCell(new Paragraph("", new Font(Font.FontFamily.HELVETICA, 6f, Font.NORMAL))) { HorizontalAlignment = Element.ALIGN_RIGHT, Border = 1 }); */ } pck.Save(); pck.Dispose(); }
public static Double AllotmentTotalRealignment(Int32 ID, out Double after_realignment, out Double AsOfCurrentDate) { BudgetDB db = new BudgetDB(); Double total = 0.00; Double percentage = 0.00; var fsh = db.fsh.Where(p => p.allotment == ID.ToString() && p.type == "REG" && p.active == 1).ToList(); Double realignment_subtotal = 0.00; Double total_for_the_month = 0.00; Double total_asof_the_month = 0.00; Double unobligated_balance_allotment = 0.00; Double disbursements = 0.00; foreach (FundSourceHdr _fsh in fsh) { total = 0; //DISPLAY PREXC CODE var fsa = (from list in db.fsa join expensecode in db.uacs on list.expense_title equals expensecode.Title where list.fundsource == _fsh.ID.ToString() select new { ID = list.ID, ExpenseTitle = list.expense_title, ExpenseCode = expensecode.Code, Amount = list.amount } ).ToList(); total = 0; foreach (var _fsa in fsa) { Double _fsa_amount = _fsa.Amount; var realignments_from = (from realignment in db.realignment join _rel_fsh in db.fsh on realignment.fundsource equals _rel_fsh.ID.ToString() join _rel_allotment in db.allotments on _rel_fsh.allotment equals _rel_allotment.ID.ToString() where realignment.uacs_from == _fsa.ExpenseTitle && realignment.fundsource == _fsh.ID.ToString() && _rel_allotment.ID == ID select new { Amount = realignment.amount }).ToList(); Double total_realignment = 0; String total_realignment_str = ""; if (realignments_from.Count > 0) { foreach (var amount in realignments_from) { total_realignment += amount.Amount; } _fsa_amount -= total_realignment; total_realignment_str = total_realignment > 0 ? "(" + total_realignment.ToString("N", new CultureInfo("en-US")) + ")" : ""; } else { var realignments_to = (from realignment in db.realignment join _rel_fsh in db.fsh on realignment.fundsource equals _rel_fsh.ID.ToString() join _rel_allotment in db.allotments on _rel_fsh.allotment equals _rel_allotment.ID.ToString() where realignment.uacs_to == _fsa.ExpenseTitle && realignment.fundsource == _fsh.ID.ToString() && _rel_allotment.ID == ID select new { Amount = realignment.amount }).ToList(); foreach (var amount in realignments_to) { total_realignment += amount.Amount; } _fsa_amount += total_realignment; } //realignments //realignment to //total after realignment realignment_subtotal += _fsa_amount; var uacs_amounts = (from ors_uacs in db.ors_expense_codes join ors in db.ors on ors_uacs.ors_obligation equals ors.ID join allotment in db.allotments on ors.allotment equals allotment.ID where ors.Date <= DateTime.Now && ors.FundSource == _fsh.Code && allotment.ID == ID && ors_uacs.uacs == _fsa.ExpenseTitle select new { Amount = ors_uacs.amount }).ToList(); Double month_total = 0; foreach (var amount in uacs_amounts) { month_total += amount.Amount; } total_for_the_month += month_total; //total for the month var total_utilized = (from ors_uacs in db.ors_expense_codes join ors in db.ors on ors_uacs.ors_obligation equals ors.ID join allotment in db.allotments on ors.allotment equals allotment.ID where ors.FundSource == _fsh.Code && ors.Date <= DateTime.Now && allotment.ID == ID && ors_uacs.uacs == _fsa.ExpenseTitle select new { Amount = ors_uacs.amount }).ToList(); Double total_utilized_amount = 0; foreach (var amount in total_utilized) { total_utilized_amount += amount.Amount; } total_asof_the_month += total_utilized_amount; //total as of this month //unobligated computation - subtotal per fund source unobligated_balance_allotment += (_fsa_amount - total_utilized_amount); //total unobligated //disbursements DateTime date2 = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")); var ors_disbursements = (from ors_uacs in db.ors_expense_codes join ors in db.ors on ors_uacs.ors_obligation equals ors.ID join allotment in db.allotments on ors.allotment equals allotment.ID where ors.FundSource == _fsh.Code && allotment.ID == ID && ors_uacs.uacs == _fsa.ExpenseTitle select new { Disbursements = ors_uacs.TaxAmount + ors_uacs.NetAmount + ors_uacs.Others }).ToList(); Double uacs_disbursement_total = 0.00; if (ors_disbursements != null && ors_disbursements.Count > 0) { foreach (var _ors_disbursements in ors_disbursements) { uacs_disbursement_total += _ors_disbursements.Disbursements; } } disbursements += uacs_disbursement_total; total += _fsa.Amount; } } after_realignment = realignment_subtotal; AsOfCurrentDate = disbursements; return(total); }