public static List <string> GetFreeRoomIDs()
{
List <string> allFreeRooms;
using (var context = new HotelFinalProjectEntities1())
{
// Get all bookedrooms
var allBookedRooms = context.Bookings.Where(b => b.FromDate >= DateTime.Today || b.FromDate <= DateTime.Today && DateTime.Today <= b.ToDate)
.Select(r => new { r.RoomIDFK, r.FromDate, r.ToDate });
// Get all rooms
var allRooms = context.Rooms.Select(r => r.RoomID);
//Create a list of all free rooms
allFreeRooms = new List <string>(allRooms.ToList());
foreach (var bookedroom in allBookedRooms)
{
// Remove unavailable room from all rooms
if ((bookedroom.FromDate <= bookedFrom && bookedFrom < bookedroom.ToDate) || (bookedFrom > bookedroom.FromDate && bookedTo <= bookedroom.ToDate || bookedFrom < bookedroom.FromDate && bookedTo >= bookedroom.ToDate) || (bookedroom.FromDate < bookedFrom && bookedFrom < bookedroom.ToDate && bookedroom.ToDate < bookedTo))
{
if (allFreeRooms.Contains(Convert.ToString(bookedroom.RoomIDFK)))
{
allFreeRooms.Remove(Convert.ToString(bookedroom.RoomIDFK));
}
}
}
return(allFreeRooms);
}
}
public static Boolean InsertBooking()
{
string message = "Cannot add the same reference number!";
string caption = "Error";
MessageBoxButtons button = MessageBoxButtons.OK;
try
{
using (var context = new HotelFinalProjectEntities1())
{
var booking = new Booking();
booking.BookedID = bookingReference;
booking.RoomIDFK = roomBookedID;
booking.RoomType = roomType;
booking.FromDate = bookedFrom;
booking.ToDate = bookedTo;
booking.Cost = roomCost;
context.Bookings.Add(booking);
context.SaveChanges();
}
return(true);
}
catch (Exception)
{
MessageBox.Show(message, caption, button);
return(false);
}
}
public void InsertCustomer()
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = new Customer();
customer.CustomerID = customerID;
customer.BookedIDFK = bookedIDFK;
customer.FirstName = firstName;
customer.LastName = lastName;
customer.Address = address;
customer.Phone = phone;
customer.City = city;
customer.Country = country;
customer.Licence = licence;
customer.NumberOfGuest = getNumOfGuest();
customer.RoomBooked = getRoomNumber();
customer.BookedFromDate = bookedFromDate;
customer.BookedToDate = bookedToDate;
//customer.CheckIn = checkInDate;
//customer.CheckOut = checkOutDate;
customer.Balance = balance;
context.Customers.Add(customer);
context.SaveChanges();
}
}
public static List <string> GetTodayFreeRoomIDs()
{
List <string> allToday_FreeRooms;
using (var context = new HotelFinalProjectEntities1())
{
// Get all bookedrooms
var allBookedRooms = context.Bookings.Where(b => b.FromDate == DateTime.Today || b.FromDate < DateTime.Today && DateTime.Today < b.ToDate)
.Select(r => new { r.RoomIDFK, r.FromDate, r.ToDate }).ToList();
// Get all rooms
var allRooms = context.Rooms.Select(r => r.RoomID);
allToday_FreeRooms = new List <string>(allRooms.ToList());
AllRoomIDs = new List <string>(allRooms.ToList());
// Remove bookedroom from all rooms
foreach (var bookedroom in allBookedRooms)
{
//if (allToday_FreeRooms.Contains(Convert .ToString(bookedroom.RoomIDFK )) || bookedroom.FromDate<=DateTime.Today && DateTime.Today<=bookedroom.ToDate){
if (allToday_FreeRooms.Contains(Convert.ToString(bookedroom.RoomIDFK)))
{
allToday_FreeRooms.Remove(Convert.ToString(bookedroom.RoomIDFK));
}
}
return(allToday_FreeRooms);
}
}
/*
* Get all room details
*/
public void GetAllRoomDetails()
{
using (var context = new HotelFinalProjectEntities1())
{
var room = context.Rooms.Select(r => new { r.RoomID, r.RoomType, r.Rate });
dgvAllRooms.DataSource = room.ToList();
}
}
/*
* Get all uncleaned rooms
*/
public void GetUncleanedRooms()
{
using (var context = new HotelFinalProjectEntities1())
{
var room = context.Customers.Where(r => r.CheckOut == DateTime.Today && r.RoomStatus.Equals(null)).Select(o => new { o.RoomBooked, o.CheckIn, o.CheckOut });
dgvAllUncleanedRooms.DataSource = room.ToList();
}
}
/*
* This will show all customers who will check in today
*/
public void CustomerToCheckOut()
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = context.Customers.Where(c => !(c.CheckIn.Equals(null)) && c.CheckOut.Equals(null)).Select(c => new { c.CustomerID, c.BookedIDFK, c.FirstName, c.RoomBooked, c.BookedToDate, c.CheckIn, c.CheckOut });
dgvCustomerToCheckOut.DataSource = customer.ToList();
}
}
/*
* This will show all customers who will check in today
*/
public void CustomerToCheckIn()
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = context.Customers.Where(c => c.BookedFromDate == DateTime.Today && c.CheckOut.Equals(null)).Select(c => new { c.CustomerID, c.BookedIDFK, c.FirstName, c.LastName, c.BookedFromDate, c.CheckIn });
dgvBookedCustomers.DataSource = customer.ToList();
}
}
/*
* Get the detail of customer who is in the hotel
*/
public void GetInHotelCustomerDetails(string room_ID)
{
using (var context = new HotelFinalProjectEntities1())
{
var detail = context.Customers.Where(c => c.RoomBooked == room_ID && c.CheckOut.Equals(null) && c.BookedToDate > DateTime.Today).Select(o => new { o.FirstName, o.LastName, o.BookedFromDate, o.BookedToDate }).ToList();
dgvInHouseCustomer.DataSource = detail;
}
}
/*
* This will show all customer who just checked out today
*/
public void TodayCheckedOutCustomers()
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = context.Customers.Where(c => c.CheckOut == DateTime.Today).Select(c => new { c.FirstName, c.LastName, c.RoomBooked, c.Balance });
dgvTodayCheckOutCustomers.DataSource = customer.ToList();
}
}
/*
* This will show all booking customers who made the booking
*/
public static void BookingCustomersNotYetCheckIn()
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = context.Customers.Where(c => c.BookedFromDate >= DateTime.Today && c.CheckIn.Equals(null)).Select(c => new { c.CustomerID, c.FirstName, c.BookedIDFK, c.BookedFromDate, c.BookedToDate });
dgvBookingCustomersNotYetCheckIn.DataSource = customer.ToList();
}
}
/*
* Get all room types
*/
public static HashSet <string> GetRoomTypes()
{
// Use HashSet as it stores no duplicate items
HashSet <string> allRoomTypes;
using (var context = new HotelFinalProjectEntities1())
{
var type = context.Rooms.Select(t => t.RoomType);
allRoomTypes = new HashSet <string>(type.ToList());
}
return(allRoomTypes);
}
public static List <string> getPassword()
{
List <string> allPasswords;
using (var context = new HotelFinalProjectEntities1())
{
var key = context.Logins.Select(r => r.Password);
allPasswords = new List <string>(key.ToList());
}
return(allPasswords);
}
public static List <string> getUsername()
{
List <string> allUserNames;
using (var context = new HotelFinalProjectEntities1())
{
var name = context.Logins.Select(r => r.Name);
allUserNames = new List <string>(name.ToList());
}
return(allUserNames);
}
public static double GetCheckedOutBalance(string cust_id)
{
double checkedout_balance = 0;
using (var context = new HotelFinalProjectEntities1())
{
var balance = context.Customers.Where(c => c.CustomerID == cust_id).Select(b => b.Balance).ToList();
foreach (var item in balance)
{
checkedout_balance = Convert.ToDouble(item);
}
} return(checkedout_balance);
}
// Maybe dont need this
public static DateTime GetCheckedOutDate(string cust_ID)
{
using (var context = new HotelFinalProjectEntities1())
{
DateTime date = new DateTime();
var checkIn_dates = context.Customers.Where(c => c.CustomerID == cust_ID).Select(o => o.CheckOut).ToList();
foreach (var item in checkIn_dates)
{
date = Convert.ToDateTime(item);
}
return(date);
}
}
/*
* Count number of Today check out
*/
public int GetNumOfTodayCheckOut()
{
int num_CheckOut = 0;
using (var context = new HotelFinalProjectEntities1())
{
var count = (from o in context.Customers
where o.CheckOut == DateTime.Today
select o).Count();
num_CheckOut = count;
}
return(num_CheckOut);
}
public static void AvailableRooms()
{
//dgvAvailableRooms = new DataGridView();
List <string> allRoomsIDs = new List <string>();
allRoomsIDs = GetFreeRoomIDs();
using (var context = new HotelFinalProjectEntities1())
{
var freeroom = context.Rooms.Where(r => allRoomsIDs.Contains(r.RoomID))
.Select(l => new { l.RoomID, l.RoomType, l.Rate });
dgvAvailableRooms.DataSource = freeroom.ToList();
}
}
/*
* This function will return a checked out room type
*/
public string GetCheckedOutRoomType(string booked_id)
{
string roomtype = "";
using (var context = new HotelFinalProjectEntities1())
{
var room = context.Bookings.Where(t => t.BookedID == booked_id).Select(r => r.RoomType).ToList();
foreach (var item in room)
{
roomtype = item;
}
}
return(roomtype);
}
/*
* Return room rate for room type Twin
*/
private static int Twin_Rate()
{
int roomrate = 0;
using (var context = new HotelFinalProjectEntities1())
{
var rate = context.Rooms.Where(t => t.RoomType == "Twin").Select(r => r.Rate).ToList();
foreach (var item in rate)
{
roomrate = item.Value;
}
return(roomrate);
}
}
/*
* Get the details of customer who didnt appear to check in
*/
public string GetBookingIDOfCustomerNotAppearToCheckIN()
{
string ID = "";
using (var context = new HotelFinalProjectEntities1())
{
var bookingID = context.Customers.Where(c => c.BookedToDate <= DateTime.Today && c.CheckIn.Equals(null) && c.CheckOut.Equals(null)).Select(r => r.BookedIDFK).ToList();
foreach (var item in bookingID)
{
ID = item.ToString();
}
}
return(ID);
}
public static void DeleteBooking(string booking_id)
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var booking = (from b in context.Bookings where b.BookedID == booking_id select b).SingleOrDefault();
context.Bookings.Remove(booking);
context.SaveChanges();
// MessageBox.Show("Booking with ID " + booking_id + " has been successfully removed.");
}
}
catch (Exception)
{
// MessageBox.Show("Please select Booking Id to cancel the reservation!");
}
}
/*
* Delete customer when the reservation was cancelled
*/
public void DeleteCustomer(string ID)
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var customer = (from c in context.Customers where c.BookedIDFK == ID select c).SingleOrDefault();
context.Customers.Remove(customer);
context.SaveChanges();
// MessageBox.Show("Customer has been successfully removed.");
}
}
catch (Exception)
{
//MessageBox.Show("Please select customer to cancel the reservation.");
}
}
public static void InsertPayment()
{
using (var context = new HotelFinalProjectEntities1())
{
Payment payment = new Payment();
payment.CustomerIDFK = custIDFK;
payment.PaymentDate = DateTime.Today;
payment.FirstDateOccupied = firstDateOccupied;
payment.LastDateOccupied = lastDateOccupied;
payment.PaymentType = paymentType;
payment.TotalDays = totalDays;
payment.TaxRate = taxRate;
payment.TaxAmount = taxAmount;
payment.PaymentTotal = paymentTotal;
context.Payments.Add(payment);
context.SaveChanges();
}
}
/*
* Update room status after it was cleaned up
*/
public void UpdateRoomStatus(string checkedout_roomID)
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var update = context.Customers.Where(r => r.RoomBooked == checkedout_roomID);
foreach (var item in update)
{
item.RoomStatus = "Clean";
}
context.SaveChanges();
MessageBox.Show("Room " + checkedout_roomID + " is now clean !");
}
}
catch (Exception)
{
throw;
}
}
/*
* This function below will update the room rate ,
* If user wants to change the room cost
*/
public void UpdateRoomRate(string room_Type)
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var update = context.Rooms.Where(r => r.RoomType == room_Type);
foreach (var item in update)
{
item.Rate = roomRate;
}
context.SaveChanges();
MessageBox.Show("Room " + room_Type + " has been updated !");
}
}
catch (Exception)
{
throw;
}
}
/*
* This method is get the payment details based on customer ID
*/
public void GetPaymentDetail(string cust_ID)
{
using (var context = new HotelFinalProjectEntities1())
{
var payment = (from o in context.Payments
where o.CustomerIDFK == cust_ID
select o).ToList();
foreach (var item in payment)
{
custIDFK = item.CustomerIDFK;
paymentDate = Convert.ToDateTime(item.PaymentDate);
firstdateOccupied = Convert.ToDateTime(item.FirstDateOccupied);
lastdateOccupied = Convert.ToDateTime(item.LastDateOccupied);
paymentType = item.PaymentType;
totalDay = Convert.ToInt32(item.TotalDays);
taxRate = Convert.ToDouble(item.TaxRate);
taxAmount = Convert.ToDouble(item.TaxAmount);
paymentTotal = Convert.ToDouble(item.PaymentTotal);
}
}
}
/*
* Update customer table after checked in
*/
public void UpdateAfterCheckedIn()
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var update = context.Customers.Where(c => c.CustomerID == customerSelectedIdFromDGV).First();
if (update.CheckIn.Equals(null))
{
update.CheckIn = DateTime.Today;
context.SaveChanges();
}
else
{
MessageBox.Show("The customer has already checked in !");
}
}
}
catch (Exception)
{
}
}
public static string AddUser()
{
using (var context = new HotelFinalProjectEntities1())
{
var user = new Login();
if (username == Convert.ToString("") || password == Convert.ToString(""))
{
return(" Name or password cannot be empty!");
}
else
{
user.Name = username;
user.Password = password;
context.Logins.Add(user);
context.SaveChanges();
}
return(" Successfully added a new log-in !");
//user.Name = username;
//user.Password = password;
//context.Logins.Add(user);
//context.SaveChanges();
}
}
/*
* Update customer table after checked out
*/
public void UpdateAfterCheckedOut()
{
try
{
using (var context = new HotelFinalProjectEntities1())
{
var update = context.Customers.Where(c => c.CustomerID == custCheckOutSelectedIdFromDGV).First();
if (update.CheckOut.Equals(null))
{
update.CheckOut = DateTime.Today;
// Set Balance to 0 atfter checked out
update.Balance = 0.0;
context.SaveChanges();
}
else
{
MessageBox.Show("The customer has already checked out !");
}
}
}
catch (Exception)
{
}
}
