public static void AddBudgetEntryListByYear(HotelBudget hotelEntry, string username)
{
using (var hdc = new HotelDataEntryDataContext())
{
for (var i = 0; i < 12; i++)
{
hdc.BudgetEntries.InsertOnSubmit(new BudgetEntry()
{
HotelBudgetId = hotelEntry.HotelBudgetId,
OccupancyRoom = 0,
RoomBudget = 0.00,
FBBudget = 0.00,
SpaBudget = 0.00,
Others = 0.00,
Total = 0.00,
UpdateDateTime = DateTime.Now,
UpdateUser = username,
PositionMonth = (i + 1) + "/" + hotelEntry.Year
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
public static List <BudgetEntry> ListBudgetEntryByYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var revenueEntryList = hdc.BudgetEntries.Where(item => item.HotelBudgetId == hotelEntry.HotelBudgetId).ToList();
return(revenueEntryList);
}
public static HotelDataEntryLib.HotelBudget AddHotelEntryListByYear(HotelBudget hotelEntry)
{
HotelBudget hotelEntrySubmit;
using (var hdc = new HotelDataEntryDataContext())
{
hotelEntrySubmit = new HotelBudget()
{
PropertyId = hotelEntry.PropertyId,
Year = hotelEntry.Year,
UpdateDateTime = DateTime.Now
};
hdc.HotelBudgets.InsertOnSubmit(hotelEntrySubmit);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
return(hotelEntrySubmit);
}
public static HotelBudget GetHotelEntry(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var hEntry = hdc.HotelBudgets.Single(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(hEntry);
}
public static bool ExistYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var count = hdc.HotelBudgets.Count(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(count != 0);
}
public static List<Reports> BudgetReport(HotelBudget hBudget)
{
var hdc = new HotelDataEntryDataContext();
List<Reports> list = null;
list = (from hotelBudget in hdc.HotelBudgets
join budgetEntry in hdc.BudgetEntries on hotelBudget.HotelBudgetId equals budgetEntry.HotelBudgetId
where hotelBudget.PropertyId == hBudget.PropertyId && hotelBudget.Year == hBudget.Year
orderby budgetEntry.BudgetId
select new Reports()
{
BudgetId = budgetEntry.BudgetId,
MonthYear = budgetEntry.PositionMonth,
OccupancyRoomBudget = budgetEntry.OccupancyRoom,
FBBudget = budgetEntry.FBBudget,
SpaBudget = budgetEntry.SpaBudget,
RoomBudget = budgetEntry.RoomBudget,
OtherBudget = budgetEntry.Others,
OccupancyRoomActual = 0,
FBActual = 0.00,
RoomActual = 0.00,
SpaActual = 0.00,
OtherActual = 0.00
}).ToList();
return list;
}
public static List<HotelRevenue> GetHotelRevenueList(HotelBudget hotelBudget)
{
var hdc = new HotelDataEntryDataContext();
return
hdc.HotelRevenues.Where(
item => item.Year == hotelBudget.Year && item.PropertyId == hotelBudget.PropertyId).ToList();
}
public static void AddBudgetEntryListByYear(HotelBudget hotelEntry, string username)
{
using (var hdc = new HotelDataEntryDataContext())
{
for (var i = 0; i < 12; i++)
{
hdc.BudgetEntries.InsertOnSubmit(new BudgetEntry()
{
HotelBudgetId = hotelEntry.HotelBudgetId,
OccupancyRoom = 0,
RoomBudget = 0.00,
FBBudget = 0.00,
SpaBudget = 0.00,
Others = 0.00,
Total = 0.00,
UpdateDateTime = DateTime.Now,
UpdateUser = username,
PositionMonth = (i+1)+"/"+hotelEntry.Year
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
public static List <Reports> BudgetReport(HotelBudget hBudget)
{
var hdc = new HotelDataEntryDataContext();
List <Reports> list = null;
list = (from hotelBudget in hdc.HotelBudgets
join budgetEntry in hdc.BudgetEntries on hotelBudget.HotelBudgetId equals budgetEntry.HotelBudgetId
where hotelBudget.PropertyId == hBudget.PropertyId && hotelBudget.Year == hBudget.Year
orderby budgetEntry.BudgetId
select new Reports()
{
BudgetId = budgetEntry.BudgetId,
MonthYear = budgetEntry.PositionMonth,
OccupancyRoomBudget = budgetEntry.OccupancyRoom,
FBBudget = budgetEntry.FBBudget,
SpaBudget = budgetEntry.SpaBudget,
RoomBudget = budgetEntry.RoomBudget,
OtherBudget = budgetEntry.Others,
OccupancyRoomActual = 0,
FBActual = 0.00,
RoomActual = 0.00,
SpaActual = 0.00,
OtherActual = 0.00
}).ToList();
return(list);
}
public static HotelDataEntryLib.HotelBudget AddHotelEntryListByYear(HotelBudget hotelEntry)
{
HotelBudget hotelEntrySubmit;
using (var hdc = new HotelDataEntryDataContext())
{
hotelEntrySubmit = new HotelBudget()
{
PropertyId = hotelEntry.PropertyId,
Year =hotelEntry.Year,
UpdateDateTime = DateTime.Now
};
hdc.HotelBudgets.InsertOnSubmit(hotelEntrySubmit);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
return hotelEntrySubmit;
}
public static List <HotelRevenue> GetHotelRevenueList(HotelBudget hotelBudget)
{
var hdc = new HotelDataEntryDataContext();
return
(hdc.HotelRevenues.Where(
item => item.Year == hotelBudget.Year && item.PropertyId == hotelBudget.PropertyId).ToList());
}
private void BindingJqGridReport(int year, int propertyId)
{
var hotelBudget = new HotelBudget
{
Year = year,
PropertyId = propertyId
};
var reportBudget = HotelDataEntryLib.Page.ReportsHelper.BudgetReport(hotelBudget);
var hotelRevenue = HotelDataEntryLib.Page.HotelRevenueHelper.GetHotelRevenueList(hotelBudget);
var report = CalculateReport(reportBudget, hotelRevenue);
BindReportGrid(report);
}
public static HotelBudget GetHotelEntry(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var hEntry = hdc.HotelBudgets.Single(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return hEntry;
}
public static bool ExistYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var count = hdc.HotelBudgets.Count(item => item.Year==hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return count != 0;
}
protected void btnExcelAll_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
var hb = new HotelBudget();
if (Session["year"] == null)
{
return;
}
var year = Convert.ToInt32(Session["year"].ToString());
var budget = HotelDataEntryLib.Page.BudgetHelper.GetAllPropertyByHotelBudget(year);
var attachment = "attachment; filename=All Properties" + " Budget " + _year + ".xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
for (var i = 0; i < budget.Count; i++)
{
Response.Write("[" + budget[i].CurrencyCode + "] " + budget[i].PropertyName + " Budget " + _year);
Response.Write("\r\n");
Response.Write("\r\n");
Response.Write("Month/Year\t");
Response.Write("Occupancy(%)\t");
Response.Write("Room Budget\t");
Response.Write("F & B Budget\t");
Response.Write("Spa Budget\t");
Response.Write("Others\t");
Response.Write("Total\t");
Response.Write("\n");
hb.HotelBudgetId = budget[i].HotelBudgetId;
var listBudget = BudgetHelper.ListBudgetEntryByYear(hb);
var total = CalculateTotal(listBudget);
for (var j = 0; j < listBudget.Count; j++)
{
Response.Write(listBudget[j].PositionMonth + "\t");
Response.Write(listBudget[j].OccupancyRoom.ToString("#,##0.00") + "\t");
Response.Write(listBudget[j].RoomBudget.ToString("#,##0.00") + "\t");
Response.Write(listBudget[j].FBBudget.ToString("#,##0.00") + "\t");
Response.Write(listBudget[j].SpaBudget.ToString("#,##0.00") + "\t");
Response.Write(listBudget[j].Others.ToString("#,##0.00") + "\t");
Response.Write(listBudget[j].Total.ToString("#,##0.00") + "\t");
Response.Write("\n");
}
Response.Write("Total" + "\t");
Response.Write("-" + "\t");
Response.Write(total[0].ToString("#,##0.00") + "\t");
Response.Write(total[1].ToString("#,##0.00") + "\t");
Response.Write(total[2].ToString("#,##0.00") + "\t");
Response.Write(total[3].ToString("#,##0.00") + "\t");
Response.Write(total[4].ToString("#,##0.00") + "\t");
Response.Write("\n");
Response.Write("\n");
Response.Write("\n");
}
Response.Write("\n");
Response.Write("Print Date: [" + DateTime.Now + "] ");
Response.Write("\r\n");
Response.End();
}
protected void btnPDFAll_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
var hb = new HotelBudget();
if (Session["year"] == null)
{
return;
}
var year = Convert.ToInt32(Session["year"].ToString());
var budget = HotelDataEntryLib.Page.BudgetHelper.GetAllPropertyByHotelBudget(year);
var attachment = "attachment; filename= All Properties" + " Budget " + _year + ".pdf";
var pdfDoc = new Document(PageSize.A4.Rotate(), 30.0f, 5.0f, 40.0f, 0f);
var pdfStream = new MemoryStream();
var pdfWriter = PdfWriter.GetInstance(pdfDoc, pdfStream);
pdfDoc.Open();//Open Document to write
pdfDoc.NewPage();
var fontH = FontFactory.GetFont("ARIAL", 9, Font.BOLD);
var fontT = FontFactory.GetFont("ARIAL", 12, Font.BOLD);
var font8 = FontFactory.GetFont("ARIAL", 8);
var font8B = FontFactory.GetFont("ARIAL", 8, Font.BOLD);
var prefacedate = new Paragraph {
new Paragraph("Print Date: [" + DateTime.Now + "] ", font8B)
};
var widths = new float[] { 55, 75f, 75f, 72f, 72f, 72f, 72f };
/////////////////////////////////////////////////////////////////////////////////////////////////////////
for (var i = 0; i < budget.Count; i++)
{
//Begin table
var pdfTable = new PdfPTable(7);
pdfTable.HorizontalAlignment = 0;
pdfTable.TotalWidth = 781f;
pdfTable.LockedWidth = true;
pdfTable.SetWidths(widths);
pdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table
hb.HotelBudgetId = budget[i].HotelBudgetId;
var listBudget = BudgetHelper.ListBudgetEntryByYear(hb);
var total = CalculateTotal(listBudget);
var preface = new Paragraph();
// Header
preface.Add(new Paragraph("[" + budget[i].CurrencyCode + "] " + budget[i].PropertyName + " Budget " + year, fontT));
pdfDoc.Add(preface);
PdfPCell pdfPCell = null;
//Add Header of the pdf table
pdfPCell = new PdfPCell(new Phrase(new Chunk("Month/Year", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("Occupancy(%)", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("Room Budget", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("F & B Budget", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("Spa Budget", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("Others", fontH)));
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("Total", fontH)));
pdfTable.AddCell(pdfPCell);
//How add the data from datatable to pdf table
for (var rows = 0; rows < listBudget.Count; rows++)
{
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].PositionMonth, font8)))
{
HorizontalAlignment = Element.ALIGN_LEFT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].OccupancyRoom.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].RoomBudget.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].FBBudget.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].SpaBudget.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].Others.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(listBudget[rows].Total.ToString("#,##0.00"), font8)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
}
pdfPCell = new PdfPCell(new Phrase(new Chunk("Total", font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk("-", font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(total[0].ToString("#,##0.00"), font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(total[1].ToString("#,##0.00"), font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(total[2].ToString("#,##0.00"), font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(total[3].ToString("#,##0.00"), font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfPCell = new PdfPCell(new Phrase(new Chunk(total[4].ToString("#,##0.00"), font8B)))
{
HorizontalAlignment = Element.ALIGN_RIGHT
};
pdfTable.AddCell(pdfPCell);
pdfDoc.Add(pdfTable); // add pdf table to the document
var newLine = new Paragraph();
newLine.Add(new Paragraph("", fontT));
preface.Leading = 50.0f;
pdfDoc.Add(newLine);
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////
pdfDoc.Add(prefacedate);
pdfDoc.Close();
pdfWriter.Close();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
Response.AppendHeader("Content-Disposition", attachment);
Response.BinaryWrite(pdfStream.ToArray());
Response.End();
}
protected void btnExcelAll_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
if (Session["YearReport"] == null)
{
return;
}
var y = Convert.ToInt32(Session["YearReport"].ToString());
var budget = HotelDataEntryLib.Page.BudgetHelper.GetAllPropertyByHotelBudget(y);
var attachment = "attachment; filename=All Properties" + " MTD " + _year + ".xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
for (var i = 0; i < budget.Count; i++)
{
Response.Write("[" + budget[i].CurrencyCode + "] " + budget[i].PropertyName + " MTD " + _year);
Response.Write("\r\n");
Response.Write("\r\n");
Response.Write("Month/Year\t");
Response.Write("Occupancy(%) Actual\t");
Response.Write("Occupancy(%) Budget\t");
Response.Write("Room Actual\t");
Response.Write("Room Budget\t");
Response.Write("F & B Actual\t");
Response.Write("F & B Budget\t");
Response.Write("Spa Actual\t");
Response.Write("Spa Budget\t");
Response.Write("Others Actual\t");
Response.Write("Others Budget\t");
Response.Write("\n");
var hotelBudget = new HotelBudget
{
Year = y,
PropertyId = budget[i].PropertyId
};
var reportBudget = HotelDataEntryLib.Page.ReportsHelper.BudgetReport(hotelBudget);
var hotelRevenue = HotelDataEntryLib.Page.HotelRevenueHelper.GetHotelRevenueList(hotelBudget);
var report = CalculateReport(reportBudget, hotelRevenue);
var total = CalculateTotal(report);
for (var j = 0; j < report.Count; j++)
{
Response.Write(report[j].MonthYear + "\t");
Response.Write(report[j].OccupancyRoomActual.ToString("#,##0.00") + "\t");
Response.Write(report[j].OccupancyRoomBudget.ToString("#,##0.00") + "\t");
Response.Write(report[j].RoomActual.ToString("#,##0.00") + "\t");
Response.Write(report[j].RoomBudget.ToString("#,##0.00") + "\t");
Response.Write(report[j].FBActual.ToString("#,##0.00") + "\t");
Response.Write(report[j].FBBudget.ToString("#,##0.00") + "\t");
Response.Write(report[j].SpaActual.ToString("#,##0.00") + "\t");
Response.Write(report[j].SpaBudget.ToString("#,##0.00") + "\t");
Response.Write(report[j].OtherActual.ToString("#,##0.00") + "\t");
Response.Write(report[j].OtherBudget.ToString("#,##0.00") + "\t");
Response.Write("\n");
}
Response.Write("Total" + "\t");
Response.Write("-" + "\t");
Response.Write("-" + "\t");
Response.Write(total[0].ToString("#,##0.00") + "\t");
Response.Write(total[1].ToString("#,##0.00") + "\t");
Response.Write(total[2].ToString("#,##0.00") + "\t");
Response.Write(total[3].ToString("#,##0.00") + "\t");
Response.Write(total[4].ToString("#,##0.00") + "\t");
Response.Write(total[5].ToString("#,##0.00") + "\t");
Response.Write(total[6].ToString("#,##0.00") + "\t");
Response.Write(total[7].ToString("#,##0.00") + "\t");
Response.Write("\n");
Response.Write("\n");
Response.Write("\n");
}
Response.Write("\n");
Response.Write("Print Date: [" + DateTime.Now + "] ");
Response.Write("\r\n");
Response.End();
}
public static List<BudgetEntry> ListBudgetEntryByYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var revenueEntryList = hdc.BudgetEntries.Where(item => item.HotelBudgetId == hotelEntry.HotelBudgetId).ToList();
return revenueEntryList;
}
private void ShowData(int propertyId, string my)
{
if (string.IsNullOrEmpty(my) || propertyId <= 0)
{
lbError.Visible = true;
lbCalendar.Visible = true;
lbCompany.Visible = true;
divReport.Attributes["style"] = "display:none";
}
else
{
lbError.Visible = false;
lbCalendar.Visible = false;
lbCompany.Visible = false;
divReport.Attributes["style"] = "";
divJqgrid.Attributes["style"] = "";
var username = Session["UserSession"].ToString();
var user = UserHelper.GetUser(username);
if (user.PropertyId == 15)//OHG ID
{
divExportAllData.Attributes["style"] = "";
}
var str = my.Split('/');
if (!string.IsNullOrEmpty(str[0]) && !string.IsNullOrEmpty(str[1]))
{
Session["PropertyIdReport"] = propertyId; //for reports.aspx property
Session["YearReport"] = Convert.ToInt32(str[1]); //for reports.aspx year
var hotelEntry = new HotelDataEntryLib.HotelRevenue()
{
PropertyId = propertyId,
Month = Convert.ToInt32(str[0]),
Year = Convert.ToInt32(str[1])
};
if (HotelRevenueHelper.ExistMothYear(hotelEntry))
{
var exsitEntry = HotelRevenueHelper.GetHotelEntry(hotelEntry);
BindDataEntryJqgrid(exsitEntry);
}
else
{
var budgetEntry = new HotelBudget()
{
PropertyId = hotelEntry.PropertyId,
Year = hotelEntry.Year
};
if (!HotelBudgetHelper.ExistYear(budgetEntry))
{
var newBudgetEntry = HotelBudgetHelper.AddHotelEntryListByYear(budgetEntry);
BudgetHelper.AddBudgetEntryListByYear(newBudgetEntry, Session["UserSession"].ToString());
}
var newEntry = HotelRevenueHelper.AddHotelEntryListByMonthYear(hotelEntry);
RevenueHelper.AddRevenueEntryListByMonthYear(newEntry, Session["UserSession"].ToString());
BindDataEntryJqgrid(newEntry);
}
}
}
}
