public IEnumerable GuestsWithoutCheckOut()
{ //get the guest data who need to check Out
using (var context = new SunShine_HotelEntities())
{
var CheckOutEmpty = from g in context.Guests
join b in context.Billings on g.GuestID equals b.GuestIDFK
where g.CheckIn != null && g.CheckOut == null
orderby g.GuestID descending
select new
{
g.GuestID,
g.BookingIDFK,
g.Name,
g.Address,
g.Phone,
g.Guests,
g.Room,
g.CheckIn,
g.CheckOut,
g.BookingDate,
b.BillingID,
b.BarCharge,
b.TeleChrage,
b.RoomCharge,
b.WiFiCharges
};
return(CheckOutEmpty.ToList());
}
}
public void NewGuest()
{ //get the current booking date
DateTime BookingDate;
BookingDate = DateTime.Now;
//get the last booking ID from the new booking, really difficult
using (var context = new SunShine_HotelEntities())
{ //get the last booking ID from the new booking, really difficult
var bookingId =
Convert.ToInt16(
context.Bookings.OrderByDescending(b => b.BookingID).Select(c => c.BookingID).First());
//var RoomID = Convert.ToInt16(
// context.Bookings.OrderByDescending(b => b.BookingID).Select(c => c.RoomIDFK).First());
var AddGuest = new Guest();
AddGuest.Name = BookingDetailsDTO.guestName;
AddGuest.Address = BookingDetailsDTO.guestAddress;
AddGuest.Guests = BookingDetailsDTO.GuestNumbers;
AddGuest.Phone = BookingDetailsDTO.GuestPhoneNo;
AddGuest.Room = BookingDetailsDTO.roomID;
AddGuest.BookingDate = BookingDate;
AddGuest.BookingIDFK = bookingId;
context.Guests.Add(AddGuest);
context.SaveChanges();
}
}
public void NewBooking()
{ // adding new booking to the booking table
using (var context = new SunShine_HotelEntities())
{
var AddBooking = new Booking();
{
AddBooking.RoomIDFK = BookingDetailsDTO.roomID;
AddBooking.BookingFrom = BookingDetailsDTO.BookingFrom;
AddBooking.BookingTo = BookingDetailsDTO.BookingTo;
AddBooking.RoomCost = BookingDetailsDTO.roomCost;
}
if (AddBooking.RoomIDFK == 0 && AddBooking.RoomCost == 0)
{
MessageBox.Show(" Room No and Room Cost not selected");
}
else
{
context.Bookings.Add(AddBooking);
MessageBox.Show("Room : " + BookingDetailsDTO.roomID + " " + "has been booked Between : "
+ "\n" + BookingDetailsDTO.BookingFrom + " - " + BookingDetailsDTO.BookingTo
+ "\n" + " At total cost of : $" + BookingDetailsDTO.roomCost);
//save the changes
context.SaveChanges();
myguest.NewGuest();
myBilling.addBilling();
}
}
}
public int LastBookingId()
{
using (var context = new SunShine_HotelEntities())
{
//get all the bookings after today
var lastBookedRooms = context.Bookings.Select(b => b.BookingID).LastOrDefault();
return(lastBookedRooms);
}
}
public DateTime checkInDate(int bookingID)
{
using (var context = new SunShine_HotelEntities())
{
DateTime Date = Convert.ToDateTime(
context.Bookings.FirstOrDefault(b => b.BookingID == bookingID).BookingFrom
);
return(Date);
}
}
public static List <Room> RoomClickedDetails(IEnumerable <int> allRoomsFree)
{
//var RoomDetail = new DataGridView();
using (var context = new SunShine_HotelEntities())
{
var Allrooms = context.Rooms.Where(r => allRoomsFree.Contains(r.RoomID)).ToList();
return(Allrooms);
}
}
//public void LoadBookings()
//{
// ChooseRoomForBookingDgv = Database.ListAllBookingsAfterToday();
//}
//public void BookRoomClick()
//{
// //Book rooms between dates
// _isRoomConflict = false; //set the conflict
// //get all the dates for that room to compare them with the dates entered
// using (var context = new SunShine_HotelEntities())
// {
// var roomBookingConflict =
// context.Bookings.Where(b => b.RoomIDFK == RoomIdfk && b.BookingFrom >= DateTime.Today.Date)
// .Select(b => new { b.BookingFrom, b.BookingTo })
// .ToList();
// foreach (var bdate in roomBookingConflict)
// {
// //if the new booked start date is bigger than the existing start date and less than the existing end date or the new booked end date is bigger than existing start date and before the existing end date - conflict!
// if (BookingDetailsDTO.BookingTo.Date >= bdate.BookingFrom && BookingDetailsDTO.BookingFrom.Date < bdate.BookingTo ||
// BookingDetailsDTO.BookingTo.Date >= bdate.BookingFrom && BookingDetailsDTO.BookingTo.Date < bdate.BookingTo)
// {
// //throw a message
// MessageBox.Show("The room is already booked between " + BookingDetailsDTO.BookingFrom.Date + " and " +
// BookingDetailsDTO.BookingTo.Date);
// //exit out if there is a conflict
// _isRoomConflict = true;
// return;
// }
// } //end the foreach
// //add new booking if there is no conflict
// if (_isRoomConflict == false)
// {
// NewBooking();
// //must book the room before you book the guest
// //_allGuests.AddGuest();
// }
// }
//}
public void deleteBooking(int bookingId)
{
using (var context = new SunShine_HotelEntities())
{
var delete = context.Bookings.FirstOrDefault(b => b.BookingID == bookingId);
context.Bookings.Remove(delete);
context.SaveChanges();
MessageBox.Show(" Selected booking has been deleted successfully ....");
}
}
public void DeleteGuest(int guestID)
{
using (var context = new SunShine_HotelEntities())
{
var deleteGuest = context.Guests.FirstOrDefault(g => g.GuestID == guestID);
context.Guests.Remove(deleteGuest);
context.SaveChanges();
MessageBox.Show("Guest has been deleted successfully ....");
}
}
public void updateGuest(int guestId)
{
using (var context = new SunShine_HotelEntities())
{
var update = context.Guests.FirstOrDefault(g => g.GuestID == guestId);
update.Name = BookingDetailsDTO.guestName;
update.Address = BookingDetailsDTO.guestAddress;
update.Phone = BookingDetailsDTO.GuestPhoneNo;
update.Guests = BookingDetailsDTO.GuestNumbers;
update.Room = BookingDetailsDTO.roomID;
context.SaveChanges();
}
}
public void updateBilling(int billingID, decimal barCharge, decimal phonecharge, decimal roomcharge, decimal wifiCharge)
{
using (var context = new SunShine_HotelEntities())
{
var update = context.Billings.FirstOrDefault(b => b.BillingID == billingID);
update.BarCharge = barCharge;
update.WiFiCharges = wifiCharge;
update.TeleChrage = phonecharge;
update.RoomCharge = roomcharge;
context.SaveChanges();
MessageBox.Show(" Expenses has been updated for the selected guest !!");
}
}
public void updateRoom(int roomID, int singleBed, int doublrBed, decimal tariffSingle, decimal tariffdouble, decimal tariffExtraPerson)
{
using (var context = new SunShine_HotelEntities())
{
var update = context.Rooms.FirstOrDefault(r => r.RoomID == roomID);
update.SingleBeds = singleBed;
update.DoubleBeds = doublrBed;
update.TariffSinglePerson = tariffSingle;
update.TariffDoublePerson = tariffdouble;
update.TariffExtraPerson = tariffExtraPerson;
context.SaveChanges();
}
}
public static IEnumerable <Room> GetFreeRoomsAndStuff(IEnumerable <int> allRoomsFree)
{
List <Room> FreeRooms = new List <Room>();
using (var context = new SunShine_HotelEntities())
{
var AllFreerooms = context.Rooms.Where(r => allRoomsFree.Contains(r.RoomID)).ToList();
//output the other details to a list
FreeRooms = AllFreerooms;
}
//returm must be outside the using statement
return(FreeRooms);
}
public void addRoom(int singleBed, int doubleBed, decimal tariffSingle, decimal tariffDouble, decimal tariffExtra)
{
using (var context = new SunShine_HotelEntities())
{
var AddRoom = new Room();
AddRoom.SingleBeds = singleBed;
AddRoom.DoubleBeds = doubleBed;
AddRoom.TariffSinglePerson = tariffSingle;
AddRoom.TariffDoublePerson = tariffDouble;
AddRoom.TariffExtraPerson = tariffExtra;
context.Rooms.Add(AddRoom);
context.SaveChanges();
}
}
public IEnumerable getBillingDetail(int gID)
{
using (var context = new SunShine_HotelEntities())
{
var billingINFO = from b in context.Billings
where b.GuestIDFK == gID
select new
{
b.BarCharge,
b.TeleChrage,
b.RoomCharge,
b.WiFiCharges
};
return(billingINFO.ToList());
}
}
public IEnumerable AllBooking()
{ // get all the bookings from the booking table
using (var context = new SunShine_HotelEntities())
{
var bookings = from b in context.Bookings.OrderByDescending(b => b.BookingID)
select new
{
b.BookingID,
b.RoomIDFK,
b.BookingFrom,
b.BookingTo,
b.RoomCost
};
return(bookings.ToList());
}
}
//confirm guest check in
public void CheckIn(int bookingId)
{
using (var context = new SunShine_HotelEntities())
{
//checkInDate = Convert.ToDateTime(
//context.Bookings.Where(b => b.BookingID == bookingId).Select(b => b.BookingFrom));
var confirm = context.Guests.FirstOrDefault(b => b.BookingIDFK == bookingId);
confirm.CheckIn = DateTime.Now;
context.SaveChanges();
}
}
public IEnumerable AllRooms(List <int> RoomID)
{ // get all the bookings from the booking table
using (var context = new SunShine_HotelEntities())
{
var bookings = from r in context.Rooms
select new
{
r.RoomID,
r.SingleBeds,
r.DoubleBeds,
r.TariffSinglePerson,
r.TariffDoublePerson,
r.TariffExtraPerson
};
return(bookings.ToList());
}
}
internal static DataGridView ListAllBookingsAfterToday()
{
using (var context = new SunShine_HotelEntities())
{
DataGridView dgv = new DataGridView();
var allBookings =
context.Bookings.Where(b => b.BookingFrom >= DateTime.Today.Date)
.Select(b => new { b.RoomIDFK, b.BookingFrom, b.BookingTo })
.OrderBy(b => b.RoomIDFK)
.ThenBy(b => b.BookingFrom);
//send it out to your datagrid view
dgv.DataSource = allBookings.ToList();
return(dgv);
}
}
//public DataGridView MyDGVGuests { get; set; }
//public DataGridView MyDGVRooms { get; set; }
public static DataGridView Load_DGVGuests()
{
using (var context = new SunShine_HotelEntities())
{
DataGridView dgv = new DataGridView();
//var guests = context.Guests.Select(g => new
//{
// g.GuestID,
// g.BookingIDFK,
// g.Name,
// g.Address,
// g.Phone,
// g.Guests,
// g.Room,
// g.CheckIn,
// g.CheckOut,
// g.BookingDate
//}).OrderByDescending(g => g.GuestID);
var guests = from g in context.Guests
join b in context.Billings on g.GuestID equals b.GuestIDFK
orderby g.GuestID descending
select new
{
g.GuestID,
g.BookingIDFK,
g.Name,
g.Address,
g.Phone,
g.Guests,
g.Room,
g.CheckIn,
g.CheckOut,
g.BookingDate,
b.BillingID,
b.BarCharge,
b.TeleChrage,
b.RoomCharge,
b.WiFiCharges
};
dgv.DataSource = guests.ToList();
return(dgv);
}
}
//adding the room charges to billing
public void addBilling()
{
using (var context = new SunShine_HotelEntities())
{
var guestId =
Convert.ToInt16(
context.Guests.OrderByDescending(g => g.GuestID).Select(c => c.GuestID).First());
var newBilling = new Billing();
newBilling.GuestIDFK = guestId;
newBilling.RoomCharge = BookingDetailsDTO.roomCost;
newBilling.BarCharge = Convert.ToDecimal("0.00");
newBilling.TeleChrage = Convert.ToDecimal("0.00");
newBilling.WiFiCharges = Convert.ToDecimal("0.00");
context.Billings.Add(newBilling);
context.SaveChanges();
}
}
public void GuestsCheckOut(int guestID, decimal total)
{
using (var context = new SunShine_HotelEntities())
{ //check if the guest checked In or not
var currentGuest = context.Guests.SingleOrDefault(g => g.GuestID == guestID);
if (currentGuest.CheckIn == null)
{
MessageBox.Show(" Guest hasn't checked in yet");
}
else
{
MessageBox.Show("Guest is ready to check out" + "\n" + "With Total Expenses to pay : $" + total);
currentGuest.CheckOut = DateTime.Now.Date;
context.SaveChanges();
}
}
}
internal void DeleteRoom(int roomID)
{
using (var context = new SunShine_HotelEntities())
{
var roomBooked = context.Bookings.FirstOrDefault(b => b.RoomIDFK == roomID);
if (roomBooked.BookingFrom > DateTime.Today.Date)
{
MessageBox.Show("Can't delete the room : " + roomID + " as its booked on : " + roomBooked.BookingFrom);
}
else
{
var deleteRoom = context.Rooms.FirstOrDefault(r => r.RoomID == roomID);
context.Rooms.Remove(deleteRoom);
context.SaveChanges();
MessageBox.Show("Room" + " " + roomID + " " + " has been deleted successfully ....");
}
}
}
//public DateTime checkInDate { get; set; }
//get everything about guests from the database and display
public IEnumerable AllGuests()
{
using (var context = new SunShine_HotelEntities())
{
var allGuests = from g in context.Guests
select new
{
g.GuestID,
g.BookingIDFK,
g.Name,
g.Address,
g.Phone,
g.Guests,
g.Room,
g.CheckIn,
g.CheckOut,
g.BookingDate
};
return(allGuests.ToList());
}
}
public static DataGridView Load_DGVBookings()
{
using (var context = new SunShine_HotelEntities())
{
DataGridView dgv = new DataGridView();
var rooms = from b in context.Bookings.OrderByDescending(b => b.BookingID)
//because you are selecting more than 1 field you must wrap it in new { }
select new
{
b.BookingID,
b.RoomIDFK,
b.BookingFrom,
b.BookingTo,
b.RoomCost
};
//send it out to your datagrid view
dgv.DataSource = rooms.ToList();
return(dgv);
}
}
public void addCharges(int gId, decimal barCharge, decimal phonecharge, decimal roomcharge, decimal wifiCharge)
{
using (var context = new SunShine_HotelEntities())
{
if ((from b in context.Billings where b.GuestIDFK == gId select b).Count() > 0)
{
MessageBox.Show("Expenses already added for this guest....");
}
else
{
var AddBilling = new Billing();
AddBilling.BarCharge = barCharge;
AddBilling.GuestIDFK = gId;
AddBilling.TeleChrage = phonecharge;
AddBilling.WiFiCharges = wifiCharge;
AddBilling.RoomCharge = roomcharge;
context.Billings.Add(AddBilling);
context.SaveChanges();
}
}
}
public static DataGridView Load_DGVBilling()
{
using (var context = new SunShine_HotelEntities())
{
DataGridView dgv = new DataGridView();
var billing = from b in context.Billings.OrderByDescending(b => b.BillingID)
//because you are selecting more than 1 field you must wrap it in new { }
select new
{
b.BillingID,
b.GuestIDFK,
b.BarCharge,
b.TeleChrage,
b.RoomCharge,
b.WiFiCharges
};
//send it out to your datagrid view
dgv.DataSource = billing.ToList();
return(dgv);
}
}
/// <summary>
/// Checks to see if the room is free before booking it
/// IS THIS NECESSARY? NOT USED
/// </summary>
public void BookRoomClick()
{
//Book rooms between dates
_isRoomConflict = false; //set the conflict
//get all the dates for that room to compare them with the dates entered
using (var context = new SunShine_HotelEntities())
{
var roomBookingConflict =
context.Bookings.Where(b => b.RoomIDFK == RoomIdfk && b.BookingFrom >= DateTime.Today.Date)
.Select(b => new { b.BookingFrom, b.BookingTo })
.ToList();
foreach (var bdate in roomBookingConflict)
{
//if the new booked start date is bigger than the existing start date and less than the existing end date or the new booked end date is bigger than existing start date and before the existing end date - conflict!
if (BookingFrom.Date >= bdate.BookingFrom && BookingFrom.Date < bdate.BookingTo ||
BookingTo.Date >= bdate.BookingFrom && BookingTo.Date < bdate.BookingTo)
{
//throw a message
MessageBox.Show("The room is already booked between " + BookingFrom.Date + " and " +
BookingTo.Date);
//exit out if there is a conflict
_isRoomConflict = true;
return;
}
} //end the foreach
//add new booking if there is no conflict
if (_isRoomConflict == false)
{
AddNewBooking();
//must book the room before you book the guest
//_allGuests.AddGuest();
}
}
}
public void GuestBookingInfo(int bookingID)
{
using (var context = new SunShine_HotelEntities())
{
int roomID = 0;
DateTime bookingFrom = new DateTime(), BookingTo = new DateTime();
decimal roomCost = 0;
{
var loadGuestBooking =
context.Bookings.Where(b => b.BookingID == bookingID)
.Select(b => new { b.BookingID, b.RoomIDFK, b.BookingFrom, b.BookingTo, b.RoomCost })
.SingleOrDefault();
roomID = loadGuestBooking.RoomIDFK;
bookingFrom = loadGuestBooking.BookingFrom.Value;
BookingTo = loadGuestBooking.BookingTo.Value;
roomCost = loadGuestBooking.RoomCost.Value;
//return bookingInfo.ToList();
}
MessageBox.Show("Room :" + roomID + " " + "is booked from " + bookingFrom + " - " + BookingTo
+ "\n" + " At the Cost of : $" + roomCost + " " + " For this Guest.");
}
}
/// <summary>
/// Adds a new booking if there isn't a conflict
/// this runs from the BookRoomClick method above if there is no date conflict
/// </summary>
public void AddNewBooking()
{
//make a new instance and pass that to the class
var myRB = new Booking()
{
RoomIDFK = RoomIdfk,
BookingFrom = BookingFrom,
BookingTo = BookingTo,
RoomCost = RoomBookedPrice()
};
if (myRB.RoomIDFK != 0 && myRB.RoomCost != 0)
{
using (var context = new SunShine_HotelEntities())
{
context.Bookings.Add(myRB);
context.SaveChanges();
}
}
else
{
MessageBox.Show(myRB.RoomIDFK + " RoomIDFK is null " + myRB.RoomCost + " Roomcost is null");
}
}
public static List <int> GetFreeRooms()
{
List <int> allRoomsFreeId;
using (var context = new SunShine_HotelEntities())
{
//get all the bookings after today
var allBookedRooms =
context.Bookings.Where(b => b.BookingFrom >= DateTime.Today)
.Select(b => new { b.RoomIDFK, b.BookingFrom, b.BookingTo })
.OrderBy(b => b.RoomIDFK)
.ThenBy(b => b.BookingFrom);
//get all the rooms in total
var allRooms = context.Rooms.Select(r => r.RoomID);
//make a list of all the room numbers (maybe i could leave it as allrooms but whatever)
allRoomsFreeId = new List <int>(allRooms.ToList());
//loop through all the booked rooms
foreach (var bookedRoom in allBookedRooms)
{
//find if the booking dates are inside the room dates and remove the rooms from the list if they conflict
if (DoTheDatesOverlap(BookingDetailsDTO.BookingFrom, BookingDetailsDTO.BookingTo, bookedRoom.BookingFrom, bookedRoom.BookingTo))
{
if (allRoomsFreeId.Contains(Convert.ToInt32(bookedRoom.RoomIDFK)))
{
//remove rooms from the list of all rooms that are in conflict
allRoomsFreeId.Remove(Convert.ToInt32(bookedRoom.RoomIDFK));
}
}
}
}
return(allRoomsFreeId);
}