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