public int AddNewGuest(AllGuests myAG)
{
using (var context = new Sunshine_HotelEntities1())
{
//get the last booking ID from the new booking, really difficult needed to google this hard
var bookingId =
Convert.ToInt16(
context.Bookings.OrderByDescending(b => b.BookingID).Select(c => c.BookingID).First());
//CREATE new entry
var newGuest = new Guest
{
Name = myAG.Name,
Address = myAG.Address,
NumberOfGuests = myAG.NumberOfGuests,
RoomBooked = myAG.RoomBooked,
BookingIDFK = bookingId, //get the booking ID from the booking table, this
BookingDate = DateTime.Today.Date
};
// newGuest.CheckIn = DateTime.Now;
context.Guests.Add(newGuest);
context.SaveChanges();
//need to reload the guest just saved to get back the GuestID in case the checkin is immediatly done, instead of being selected from the dgv, cannot use .last as SQL doesn't recognise it
var lastGuest = context.Guests.OrderByDescending(g => g.GuestID).Select(g => g.GuestID).FirstOrDefault();
return lastGuest;
}
}
//add a new booking passing roombooking means extra unneeded data is being shunted around.
//public void AddNewBookingToDB(int RoomIdfk, DateTime BookingFrom, DateTime BookingTo, Decimal Roomcost) {
public void AddNewBookingToDB(RoomBooking myRB)
{
using (var context = new Sunshine_HotelEntities1())
{
// CREATE a new booking
var newbooking = new Booking();
newbooking.RoomIDFK = myRB.RoomIdfk;
newbooking.BookingFrom = myRB.BookingFrom.Date;
newbooking.BookingTo = myRB.BookingTo.Date;
//add in the cost of the room extracted from the dictionary
newbooking.RoomCost = myRB.roomCost;
// RoomCost = (decimal) newbooking.RoomCost;
//update db
context.Bookings.Add(newbooking);
context.SaveChanges();
var BookedConfirmationMessage = Environment.NewLine + "You have booked Room " +
myRB.RoomIdfk + Environment.NewLine + "From " +
myRB.BookingFrom + " To " + Environment.NewLine +
myRB.BookingTo + Environment.NewLine + " For " +
(string.Format("{0:C}", myRB.roomCost));
//show a confirmation message
MessageBox.Show(BookedConfirmationMessage);
}
}
public DataGridView LoadGuestsTable()
{
// using (var context = new Sunshine_HotelEntities()) {
_context = new Sunshine_HotelEntities1();
var allGuests = _context.Guests.Select(g => g);
Dgv.DataSource = allGuests.ToList();
// Dispose();
return Dgv;
}
/// <summary>
/// Load the rooms table to the dgv
/// </summary>
/// <returns>the dgv</returns>
public DataGridView LoadRoomsTable()
{
// using (var context = new Sunshine_HotelEntities()) {
_context = new Sunshine_HotelEntities1();
var allRooms =
_context.Rooms.OrderBy(r => r.RoomID);
Dgv.DataSource = allRooms.ToList();
// Dispose();
return Dgv;
}
/// <summary>
/// Room that is clicked on in the dgv for booking
/// Get the free rooms and return all their details
/// </summary>
/// <returns></returns>
public static List<Room> RoomClickedDetails(List<int> allRoomsFree)
{
using (var context = new Sunshine_HotelEntities1())
{
var roomDetails =
context.Rooms.Where(r => allRoomsFree.Contains(r.RoomID)).ToList();
//var RoomDetails = from room in context.Rooms
// where allRoomsFree.Contains(room.RoomID)
// select room;
return roomDetails;
}
}
// private RoomBooking myRoomBooking;
public static DataGridView ListAllBookingsAfterToday()
{
DataGridView chooseRoomForBookingDgv = null;
//list all bookings after today
using (var context = new Sunshine_HotelEntities1())
{
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);
chooseRoomForBookingDgv.DataSource = allBookings.ToList();
return chooseRoomForBookingDgv;
}
}
public void AddBilling(int GuestID, decimal RoomCharge)
{
using (var context2 = new Sunshine_HotelEntities1())
{
//select out the last guest ID, whichis the one above
// var NewGuestID = context.Guests.Select(g => g.GuestID).ToList().LastOrDefault();
var saveRoomBilling = new Billing
{
GuestIDFK = GuestID,
BarCharge = 0,
WiFiCharge = 0,
TelephoneCharge = 0,
RoomCharge = RoomCharge
};
context2.Billings.Add(saveRoomBilling);
context2.SaveChanges();
}
}
public bool CheckOutTheGuest(int GuestId)
{
var IsGuestCheckOut = false;
using (var context = new Sunshine_HotelEntities1())
{
//get the current guest
var currentGuest = context.Guests.SingleOrDefault(g => g.GuestID == GuestId);
// var CurrentBooking =
// context.Bookings.Where(b => b.RoomIDFK == CurrentGuest.Select(g => g.RoomBooked));
//if they havn't checked in, they can't check out
if (currentGuest.CheckIn == null)
{
MessageBox.Show("You havn't checked in yet");
return true;
}
//make sure only to get one entry, well there should only be one anyway
var goodbyeGuest = currentGuest;
goodbyeGuest.CheckOut = DateTime.Now;
context.SaveChanges();
//turn the checkout bool to true
IsGuestCheckOut = true;
}
return IsGuestCheckOut;
}
public DateTime GetDateToCheckIn(int GuestId)
{
DateTime guestBookingDateFrom;
//You can only check in on the day of the booking naturally or after that day
using (var context = new Sunshine_HotelEntities1())
{
//get the check in date
// var GuestCheckIn = context.Guests.SingleOrDefault(g => g.GuestID == this.GuestID);
guestBookingDateFrom = (DateTime) context.Guests.Where(g => g.GuestID == GuestId)
.Select(g => g.Booking.BookingFrom)
.SingleOrDefault();
//if the booking from is the same or after todays date ......
}
return guestBookingDateFrom;
}
/// <summary>
/// Get the last Booking ID
/// </summary>
/// <returns></returns>
public int LastBookingId()
{
using (var context = new Sunshine_HotelEntities1()) {
//get all the bookings after today
var lastBookedRooms = context.Bookings.Select(b => b.BookingID).LastOrDefault();
return lastBookedRooms;
}
}
/// <summary>
/// Get the rooms that are free
/// </summary>
/// <returns>List of the free rooms</returns>
public List<int> GetFreeRooms()
{
List<int> allRoomsFreeId;
using (var context = new Sunshine_HotelEntities1()) {
//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(BookingFrom, 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;
}
/// <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_HotelEntities1()) {
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 LoadBilling(int guestId)
{
//delete below?
if (guestId < 1)
{
ResetBillingToZero();
return;
}
try
{
//if there is a bill coming back then load it, this will become redundant when all the guests have bills
using (var context = new Sunshine_HotelEntities1())
{
var loadGuestBill =
context.Billings.Where(b => b.GuestIDFK == guestId)
.Select(b => new {b.GuestIDFK, b.BarCharge, b.WiFiCharge, b.TelephoneCharge, b.RoomCharge})
.SingleOrDefault();
//there is a billing list for this person
BarCharge = loadGuestBill.BarCharge.Value;
WifiCharge = loadGuestBill.WiFiCharge.Value;
PhoneCharge = loadGuestBill.TelephoneCharge.Value;
RoomCharge = loadGuestBill.RoomCharge.Value;
ExpensesTotalText = BarCharge + WifiCharge + PhoneCharge + RoomCharge;
}
}
catch (Exception e)
{
ResetBillingToZero();
MessageBox.Show("Can't load Billing", string.Format("An error occurred: '{0}'", e));
}
}
/// <summary>
/// This does Update and New entries
/// </summary>
/// <returns>The total to the label</returns>
public string SaveExpenses()
{
//save the expenses to the DB and return the Total
if (GuestIDFK == 0)
{
MessageBox.Show("You must chose a Guest first");
return "Invalid";
}
//get a list of all guestID to check against
var id = new List<int?>();
//put the guestids into the list
using (var context = new Sunshine_HotelEntities1())
{
id = context.Billings.Select(b => b.GuestIDFK).ToList();
}
//IF THERE IS ALREADY A GUEST THEN JUST UPDATE
if (id.Contains(GuestIDFK))
{
using (var context = new Sunshine_HotelEntities1())
{
//get the entry that matches the guestid
var update = context.Billings.Where(b => b.GuestIDFK == GuestIDFK);
//get the first or only entry
var bill = update.FirstOrDefault();
//pass the data across
bill.BarCharge = BarCharge;
bill.WiFiCharge = WifiCharge;
bill.TelephoneCharge = PhoneCharge;
bill.RoomCharge = RoomCharge;
bill.GuestIDFK = GuestIDFK;
//save changes
context.SaveChanges();
}
//ELSE ADD NEW GUEST
}
else
{
using (var context = new Sunshine_HotelEntities1())
{
var NewBilling = new Billing();
NewBilling.BarCharge = BarCharge;
NewBilling.WiFiCharge = WifiCharge;
NewBilling.TelephoneCharge = PhoneCharge;
NewBilling.RoomCharge = RoomCharge;
NewBilling.GuestIDFK = GuestIDFK;
//update db
context.Billings.Add(NewBilling);
context.SaveChanges();
}
}
var total = Convert.ToString(BarCharge + WifiCharge + PhoneCharge + RoomCharge);
return total;
}
public void CheckTheGuestIn(int GuestId)
{
using (var context = new Sunshine_HotelEntities1())
{
var guest = context.Guests.SingleOrDefault(g => g.GuestID == GuestId);
guest.CheckIn = DateTime.Now;
context.SaveChanges();
}
}
public string LoadGuestBooking(int GuestId )
{
using (var context = new Sunshine_HotelEntities1()) {
//get the current guest
var currentGuestBooking =
context.Guests.Where(g => g.GuestID == GuestId).Select(g => g.BookingIDFK).SingleOrDefault();
var currentBooking = context.Bookings.SingleOrDefault(b => b.BookingID == currentGuestBooking);
return "From " + currentBooking.BookingFrom.Value.Date.ToShortDateString() + " to " +
currentBooking.BookingTo.Value.Date.ToShortDateString() + " In room " +
currentBooking.RoomIDFK;
}
}
public DataGridView LoadDatesForBookedRoomsv(int RoomNumber)
{
//Get all guests who havn't checked out && g.CheckIn > DateTime.Today.Date
var mydgv = new DataGridView();
var Dates = new List<DateTime>();
using (var context = new Sunshine_HotelEntities1())
{
var loadRoomDates = from b in context.Bookings
where b.Room.RoomID == RoomNumber
//room number
orderby b.BookingFrom
select new
{
//when you change the navigation it makes a new connection booking instead of removing or replacinig the old connection booking in the model
Booking_From = b.BookingFrom,
Booking_To = b.BookingTo
};
mydgv.DataSource = loadRoomDates.ToList();
// loadRoomDates.ToList();
return mydgv;
}
}
public DataGridView LoadAllGuestsToDgv()
{
//Get all guests who havn't checked out && g.CheckIn > DateTime.Today.Date
var mydgv = new DataGridView();
using (var context = new Sunshine_HotelEntities1())
{
var loadGuests = from g in context.Guests
where g.CheckOut == null
//so there are people there
orderby g.Booking.BookingFrom
select
new
{
ID = g.GuestID,
g.Name,
g.Address,
Guests = g.NumberOfGuests,
Room = g.RoomBooked,
//when you change the navigation it makes a new connection booking instead of removing or replacinig the old connection booking in the model
From = g.Booking.BookingFrom,
To = g.Booking.BookingTo,
Check_In = g.CheckIn
};
mydgv.DataSource = loadGuests.ToList();
return mydgv;
}
}
public DataGridView GetFreeRoomsAndStuff(List<int> allRoomsFree)
{
var ChooseRoomForBookingDgv = new DataGridView();
using (var context = new Sunshine_HotelEntities1())
{
//get all the bookings, get all the booked rooms, subtract one from the other and you are left with free rooms.
//then match the free rooms by the dates booked and the guest numbers to get the best rooms
//leftover rooms are by logic free, get the rest of their details such as beds THIS IS FOR THE DGV ONLY
var freeRoomsAndBedsForDgv =
context.Rooms.Where(r => allRoomsFree.Contains(r.RoomID))
.Select(r => new {Room = r.RoomID, Singlebeds = r.NumSingleBeds, Doublebeds = r.NumDoubleBeds});
//output the other details to the dgv
ChooseRoomForBookingDgv.DataSource = freeRoomsAndBedsForDgv.ToList();
// AllDataForBooking.DataSource = freeRoomsAndBedsForDgv.ToList();
}
return ChooseRoomForBookingDgv;
}