public static void CalculateBill()
{//todo connect text boxes to datagrid cell click
DataTransition.BarCharge = Convert.ToDecimal(txtBarCharge.Text);
DataTransition.PhoneCharge = Convert.ToDecimal(txtPhoneCharge.Text);
DataTransition.WiFiCharge = Convert.ToDecimal(txtPhoneCharge.Text);
//update function
if (txtGuestName.Text != string.Empty)
{
using (var context = new ManahauMotelEntities())
{ var bill = context.Joins.FirstOrDefault(j => j.BillingIDFK == j.Billing.BillingID);
if (bill != null)
{
bill.Booking.Guest.Name = DataTransition.Name;
bill.Room.RoomID = DataTransition.RoomID;
bill.Billing.RoomCharge = DataTransition.RoomCharge;
bill.Billing.BarCharge = DataTransition.BarCharge;
bill.Billing.WiFiCharge = DataTransition.WiFiCharge;
bill.Billing.TelephoneCharge = DataTransition.PhoneCharge;
context.SaveChanges();
//DataLoading.BillForGuest();
GuestBill();
}
}
}
}
public static DataGridView Allreservations()
{
//to list all the reservations in the history of the motel in alphabetical guest order
using (var context = new ManahauMotelEntities())
{
var alldata = from j in context.Joins
orderby j.Booking.Guest.Name ascending
// orderby j.Booking.BookingFrom ascending
select new
{
j.Booking.BookingID,
j.Booking.BookingFrom,
j.Booking.BookingTo,
j.Room.RoomID,
j.Room.Room_Name,
j.Booking.Guest.Name,
j.Booking.Guest.No_ofGuests,
j.Booking.CheckIn,
j.Booking.CheckOut,
j.Booking.Guest.GuestID,
};
dgvReservations.DataSource = alldata.ToList();
}
return(dgvReservations);
}
// public static void AvailableRoom()
//public static int AvailableRoom(int? DoubleBeds, int? SingleBeds, int? ExtraBeds)
//{
// spaces += 1;
// spaces1 = (int)doublebeds;
// spaces2 = (int)singlebeds;
// spaces3 = (int)ExtraBeds;
// if (DoubleBeds == 1)
// {
// spaces1 = 2;
// }
// if(SingleBeds == 1)
// {
// spaces2 = 1;
// }
// if (ExtraBeds == 1)
// {
// spaces3 = 1;
// }
// RoomCapacity = (int)spaces1 + (int) spaces2 + (int) spaces3;
// return RoomCapacity;
// //List<Room> AllRooms = DataLoading.RoomsAvailable();
// //{
// // foreach (var room in AllRooms)
// // {
// // FillDoubleBeds(room);
// // FillSingleBeds(room);
// // FillExtraBeds(room);
// // }
// // if (doublebeds == 0 && singlebeds == 0 && extrabeds == 2)
// // {
// // MessageBox.Show("You will need an extra room.");
// //
// }
// // return; //dgvRooms;
public static DataGridView AvailableRooms()
{
DateTime wantedfrom = dtpBookFrom.Value.Date; // DateTime.Parse(txtBookedFrom.Text);
DateTime wantedto = DateTime.Parse(txtBookedTo.Text);
using (var context = new ManahauMotelEntities())
{
var room = from j in context.Joins
// where !( wantedto.Date>=j.Booking.BookingFrom && wantedto.Date <= j.Booking.BookingTo ||j.Booking.BookingTo >= wantedfrom.Date && j.Booking.BookingTo <= wantedto.Date)
where j.Booking.BookingFrom >= DateTime.Today
where
wantedfrom.Date >= j.Booking.BookingFrom && wantedfrom.Date <= j.Booking.BookingTo ||
wantedto >= j.Booking.BookingFrom && wantedto <= j.Booking.BookingTo
select j.RoomIDFK;
var freerooms = from r in context.Rooms
where !room.Contains(r.RoomID)
//Bookingfrom.Date <= wantedto.Date&& BookedTo.Date >= wantedfrom.Date
select new
{
r.RoomID,
r.Room_Name,
r.RoomType,
r.Double_Beds,
r.Single_Beds,
r.ExtraBeds,
r.Overflow
};
dgvRooms.DataSource = freerooms.ToList();
}
return(dgvRooms);
}
public static void Reservations()
{
//to get the list of reservations coming up (pending)
using (var context = new ManahauMotelEntities())
{
// var alldata = from b in context.Bookings
var alldata = from j in context.Joins
where j.Booking.BookingFrom >= DateTime.Today.Date
where j.Booking.CheckIn.Value == null
orderby j.Booking.BookingFrom ascending
select new
{
j.Booking.BookingID,
// j.Booking.Guest.GuestID,
j.Booking.BookingFrom,
j.Booking.BookingTo,
j.Room.RoomID,
j.Room.Room_Name,
j.Booking.Guest.Name,
j.Booking.Guest.No_ofGuests,
j.Booking.Guest.Notes,
j.Booking.CheckIn,
j.Booking.CheckOut,
j.Booking.Guest.GuestID,
};
//this did work and now it doesn't - something to do with the Guest IDFK being nullable but I can't change the datatable to disallow nullables & even though it tells me that I can't allow nullables it is set to allowing them AArgh!!!
dgvReservations.DataSource = alldata.ToList();
}
}
public static DataGridView LoadBills()
{
decimal WiFiCharge = 0;
decimal TelephoneCharge = 0;
decimal BarCharge = 0;
using (var context = new ManahauMotelEntities())
{
var loadBill = context.Joins.Where(j => j.Booking.GuestIDFK == j.Booking.Guest.GuestID)
.Select(
j =>
new
{
j.Booking.Guest.Name,
j.Billing.RoomCharge,
j.Billing.BarCharge,
j.Billing.TelephoneCharge,
j.Billing.WiFiCharge
});
//.SingleOrDefault();
dgvBills.DataSource = loadBill.ToList();
// DataTransition.RoomCharge = loadBill.RoomCharge;
// DataTransition.BarCharge = loadBill.BarCharge.Value;
// DataTransition.PhoneCharge = loadBill.TelephoneCharge.Value;
// DataTransition.WiFiCharge = loadBill.WiFiCharge.Value;
// DataTransition.TotalCharges =
// DataTransition.RoomCharge + DataTransition.BarCharge + DataTransition.PhoneCharge +
// DataTransition.WiFiCharge;
}
return(dgvBills);
}
public static DataGridView CurrentGuests()
{
// to show a dgv of all the guests currently checked in in order of next to check out and then by name
using (var context = new ManahauMotelEntities())
{
var alldata = from j in context.Joins
// where j.Booking.BookingFrom <= DateTime.Today.Date
where j.Booking.CheckIn <= DateTime.Now
where j.Booking.CheckOut == null
orderby j.Booking.BookingTo ascending, j.Booking.Guest.Name ascending
// where j.Booking.CheckIn != null && j.Booking.CheckIn == null
select new
{
j.Booking.BookingID,
j.Booking.BookingFrom,
j.Booking.BookingTo,
j.Room.RoomID,
j.Room.Room_Name,
j.Booking.Guest.Name,
j.Booking.Guest.No_ofGuests,
j.Booking.Guest.Notes,
j.Booking.CheckIn,
j.Booking.CheckOut,
j.Booking.Guest.GuestID,
};
dgvReservations.DataSource = alldata.ToList();
}
return(dgvReservations);
}
public static DataGridView LoadBookings()
{
//to load in the new guest and booking details
using (var context = new ManahauMotelEntities())
{
var alldata =
//context.Joins.OrderByDescending(j => j.Booking.BookingFrom).Select(j => j.Booking.BookingFrom);
from j in context.Joins
where j.Booking.BookingFrom >= DateTime.Now
//where j.Booking.CheckIn >= DateTime.Now
orderby j.Booking.BookingFrom ascending
select new
{
j.BookingIDFK,
//j.Room,
j.Booking.BookingFrom,
j.Booking.BookingTo,
j.Room.RoomID,
j.Room.Room_Name,
j.Booking.Guest.Name,
// j.Booking.Guest.Address,
// j.Booking.Guest.Phone,
j.Booking.Guest.No_ofGuests,
j.Booking.Guest.Notes,
j.Booking.CheckIn,
j.Booking.CheckOut,
j.Booking.Guest.GuestID
};
dgvReservations.DataSource = alldata.ToList();
}
return(dgvReservations);
}
public static void UpdateGuest()
{
try
{
using (var context = new ManahauMotelEntities())
{
var guest = context.Guests.SingleOrDefault(g => g.GuestID == GuestID);
//var guest = context.Joins.Where(j => j.)
//var guest = new Guest();
guest.Name = txtName.Text;
guest.Address = txtAddress.Text;
guest.Phone = txtPhone.Text;
// guest.No_ofGuests = Convert.ToInt32(txtNumGuests.Text);
guest.Notes = txtNotes.Text;
context.Guests.AddOrUpdate(guest);
context.SaveChanges();
Allguests();
MessageBox.Show(txtName.Text + " updated,");
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public static DataGridView TodaysReservations()
{
//to get the reservations for the current day only i.e. guests expected today
using (var context = new ManahauMotelEntities())
{
var alldata = from j in context.Joins
where j.Booking.BookingFrom == DateTime.Today.Date
orderby j.Booking.Guest.Name ascending
select new
{
j.Booking.BookingID,
j.Booking.BookingFrom,
j.Booking.BookingTo,
j.Room.RoomID,
j.Room.Room_Name,
j.Booking.Guest.Name,
j.Booking.Guest.No_ofGuests,
j.Booking.Guest.Notes,
j.Booking.CheckIn,
j.Booking.CheckOut,
j.Booking.Guest.GuestID,
};
dgvReservations.DataSource = alldata.ToList();
}
return(dgvReservations);
}
public static void CheckOut()
{
using (var context = new ManahauMotelEntities())
{
var booking = context.Bookings.SingleOrDefault(b => b.BookingID == BookingIDForGuest);
booking.CheckOut = DateTime.Now;
context.Bookings.AddOrUpdate();
context.SaveChanges();
}
}
public void AddGuest()
{//actually adding a booking and connecting it to a room
try
{
if (txtName.Text != String.Empty || txtAddress.Text != String.Empty || txtPhone.Text != String.Empty ||
txtNumGuests.Text != String.Empty)
{
using (var context = new ManahauMotelEntities())
{
Guest guest = new Guest();
guest.Name = txtName.Text;
guest.Address = txtAddress.Text;
guest.Phone = txtPhone.Text;
guest.No_ofGuests = Convert.ToInt32(txtNumGuests.Text);
guest.Notes = txtNotes.Text;
// var booking = new Booking();
Booking booking = new Booking();
booking.BookingFrom = dtpBookFrom.Value.Date;
booking.BookingTo = dtpBookedTo.Value.Date;
booking.GuestIDFK = guest.GuestID;
Billing billing = new Billing();
billing.RoomCharge = Convert.ToDecimal(txtCostForRoom.Text);
context.Guests.Add(guest);
context.Bookings.Add(booking);
context.Billings.Add(billing);
Room room = new Room();
room.RoomID = Convert.ToInt32(txtRoomId.Text);
Join join = new Join();
join.RoomIDFK = room.RoomID;
join.BookingIDFK = booking.BookingID;
join.BillingIDFK = billing.BillingID;
// join.BillingIDFK = billing.BillingID;
context.Joins.Add(join);
context.SaveChanges();
}
GuestDetails.Allguests();
// DataLoading.BillForGuest(GuestID);
//DataLoading.OpenAccount();
}
else
{
MessageBox.Show("Please enter all guest details thentry again.");
}
}
catch (Exception q)
{
MessageBox.Show(q.Message);
}
}
//public static List<Room> RoomsAvailable()
//{
// using (var context = new ManahauMotelEntities())
// {
// var roomDetails = context.Rooms.ToList();
// return roomDetails;
// }
//}
//todo Show reservations with booking in and out and check in and out
// list by order of date, highlight any who have missed check in
//todo get list of rooms,
//find free rooms from dates,
//todo connect list to data gridview - work out what the query shoud be
//todo add in the costs, calculating the costs for the length of stay
//check in guests
//todo Generate bill with any extra costs
//public static DataGridView LoadFreeRooms()
//{
// // allRoomsFree.AddRange(GetFreeRooms().ToList());
// //GetFreeRooms();
// var dgvRooms = new DataGridView();
// using (var context = new ManahauMotelEntities())
// {
// var freeRooms =
// context.Rooms.Where(r => allRoomsFree.Contains(r.RoomID))
// .Select(
// r =>
// new
// {
// Room = r.RoomID,
// roomName = r.Room_Name,
// roomType = r.RoomType,
// Doublebeds = r.Double_Beds,
// Singlebeds = r.Single_Beds,
// Extrabeds = r.ExtraBeds
// });
// dgvRooms.DataSource = freeRooms.ToList();
// //get the free rooms for the DGV to show
// }
// return dgvRooms;
//}
public static void ChoooseRoom()
{
using (var context = new ManahauMotelEntities())
{
var alldata = from r in context.Rooms
select new
{
r.RoomID,
r.RoomType,
r.Room_Name,
r.Double_Beds,
r.Single_Beds,
};
dgvRooms.DataSource = alldata.ToList();
}
}
public static void DeleteBooking()
{
try
{
using (var context = new ManahauMotelEntities())
{;
var contact = context.Bookings.SingleOrDefault(b => b.BookingID == BookingIDForGuest);
context.Bookings.Remove(contact);
context.SaveChanges(); }
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
public static void AllRooms()
{
// shows the data grid view with all the room details listed in it - basically a test to see if the query works
using (var context = new ManahauMotelEntities())
{
var alldata = from r in context.Rooms
select new
{
r.RoomID,
r.RoomType,
r.Room_Name,
r.Double_Beds,
r.Single_Beds,
r.ExtraBeds
};
dgvRooms.DataSource = alldata.ToList();
}
}
//public static TextBox txtBookedFrom = txtBookedFrom;
//public static TextBox txtBookedTo = txtBookedTo;
//public static TextBox txtNotes = txtNotes;
//public static string Name;
//public static string Address;
//public static string Phone;
//public static string NumGuests;
//public static string Notes;
public static void Allguests()
{
// Loads the datagrid view with all the guests in the database
using (var context = new ManahauMotelEntities())
{
var alldata = from g in context.Guests
orderby g.Name ascending
select new
{
g.GuestID,
g.Name,
g.Address,
g.Phone,
g.Notes
};
dgvGuests.DataSource = alldata.ToList();
context.SaveChanges();
}
}
public void UpdateGuest()
{
// int ID = Convert.ToInt32(txtGuestID.Text);
using (var context = new ManahauMotelEntities())
{
var guest = context.Guests.FirstOrDefault(g => g.GuestID == GuestID);
if (guest != null)
{
guest.GuestID = Convert.ToInt32(txtGuestID.Text);
guest.Name = txtName.Text;
guest.Address = txtAddress.Text;
guest.Phone = txtPhone.Text;
// guest.No_ofGuests = Convert.ToInt32(txtNumGuests.Text);
guest.Notes = txtNotes.Text;
}
//context.Guests.Add(guest);
context.SaveChanges();
ClearGuesttxtboxes();
// GuestDetails.Allguests();
}
}
public static void AddGuest()
{
{
if (txtName.Text != String.Empty || txtAddress.Text != String.Empty || txtPhone.Text != String.Empty ||
txtNumGuests.Text != String.Empty)
{
using (var context = new ManahauMotelEntities())
{
// var guest = new Guest();
Guest guest = new Guest();
guest.Name = txtName.Text;
guest.Address = txtAddress.Text;
guest.Phone = txtPhone.Text;
guest.No_ofGuests = Convert.ToInt32(txtNumGuests.Text);
guest.Notes = txtNotes.Text;
// var booking = new Booking();
Booking booking = new Booking();
booking.BookingFrom = dtpBookFrom.Value.Date;
booking.BookingTo = dtpBookedTo.Value.Date;
booking.GuestIDFK = guest.GuestID;
context.Guests.Add(guest);
context.Bookings.Add(booking);
Room room = new Room();
room.RoomID = Convert.ToInt32(txtRoomId.Text);
Join join = new Join();
join.RoomIDFK = room.RoomID;
join.BookingIDFK = booking.BookingID;
context.Joins.Add(join);
context.SaveChanges();
}
GuestDetails.Allguests();
}
}
}
public void dgvRooms_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int RoomID = 0;
decimal RoomCharge = 0;
int RoomCapacity = 0;
try
{
RoomID = (int)dgvRooms.Rows[e.RowIndex].Cells[0].Value;
if (e.RowIndex >= 0)
{
txtRoomId.Text = RoomID.ToString();
using (var context = new ManahauMotelEntities())
{
var cost =
context.Rooms.Where(r => r.RoomID == RoomID).Select(r => r.TariffCouple).FirstOrDefault();
var cost2 =
context.Rooms.Where(r => r.RoomID == RoomID)
.Select(r => r.TariffExtraPeople)
.FirstOrDefault();
int guestNumber = Convert.ToInt32(txtNumGuests.Text);
txtCostForRoom.Text = Convert.ToString(cost);
RoomCharge = BillingOperations.ChargeForExtras(guestNumber, cost, cost2);
txtCostForRoom.Text = Convert.ToString(RoomCharge);
txtRoomCharge.Text = Convert.ToString(RoomCharge);
}
}
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static DataGridView ConnectDatagrids(string GuestIDfromdgv)
{
using (var context = new ManahauMotelEntities())
{
int GuestIDagain = Convert.ToInt32(GuestIDfromdgv);
var alldata = from g in context.Guests
where g.GuestID == GuestIDagain
//var alldata = from g in context.Guests.SingleOrDefault()
// where g.GuestID == GuestID
select new
{
g.GuestID,
g.Name,
g.Address,
g.Phone,
g.Notes
};
dgvGuests.DataSource = alldata.ToList();
context.SaveChanges();
return(dgvGuests);
}
}