/// <summary>
/// Checks to see if the slot chosen overlaps with any other booking. Assumes all bookings mutually exclusive.
/// </summary>
/// <param name="bookingDate"></param>
/// <param name="facName"></param>
/// <param name="bookingFrom"></param>
/// <param name="bookingTo"></param>
/// <returns></returns>
public static bool ValidateBooking(DateTime bookingDate, string facName, int bookingFrom, int bookingTo)
{
// get full timestamps
DateTime bookingDateFrom = bookingDate + new TimeSpan(bookingFrom, 0, 0);
DateTime bookingDateTo = bookingDate + new TimeSpan(bookingTo, 0, 0);
bool isBookingValid;
SembawangSportEntities context = new SembawangSportEntities();
var q = context.Bookings
.Where(x => (x.BookingDateFrom >= bookingDateFrom && x.BookingDateTo <= bookingDateTo) || // queried timeslot sits completely inside existing timeslot
(x.BookingDateFrom <= bookingDateFrom && x.BookingDateTo >= bookingDateTo) || // exisiting timeslot sits completely inside queried timeslot
(x.BookingDateFrom <= bookingDateFrom && x.BookingDateTo > bookingDateFrom) || // exisiting timeslot overlaps with queried timeslot
(x.BookingDateFrom < bookingDateTo && x.BookingDateTo >= bookingDateTo) // exisiting timeslot overlaps with queried timeslot
)
.Where(x => x.Facility.FacilityName == facName);
if (q.Count() == 0)
{
isBookingValid = true;
}
else
{
isBookingValid = false;
}
return(isBookingValid);
}
/// <summary>
/// Get the Booking ID.
/// </summary>
/// <param name="bookingDate"></param>
/// <param name="facName"></param>
/// <param name="bookingFrom"></param>
/// <returns></returns>
static public int GetBookingID(DateTime bookingDate, string facName, int bookingFrom)
{
TimeSpan ts = new TimeSpan(bookingFrom, 0, 0);
DateTime bookingDateFrom = bookingDate + ts;
int bookingID;
// When to cache and when not to cache?
SembawangSportEntities context = new SembawangSportEntities();
var q = context.Bookings
.Where(x => x.BookingDateFrom <= bookingDateFrom &&
x.BookingDateTo > bookingDateFrom
)
.Where(x => x.Facility.FacilityName == facName).Select(x => x.BookingID);
if (q.Count() == 1)
{
bookingID = q.First();
}
else
{
bookingID = -1; // no ID found!
}
// MessageBox.Show(q.First().ToString()); // FOR DEBUG
return(bookingID);
}
public FacilityAvailabilityForm()
{
InitializeComponent();
// Cache from DB all entries for that day where the Facilities ID matches
context = new SembawangSportEntities();
// put focus on FacilityTypeCombo
facilityTypeCombo.Select();
facilityTypeCombo.Text = "- Select Facility Type -";
// leave a reference for other forms
dataGrid1 = dataGridView1;
List <Facility> flist = context.Facilities.ToList();
var list = flist.Select(x => x.FacilityType).Distinct();
foreach (var x in list)
{
facilityTypeCombo.Items.Add(x.ToString());
}
}
/// <summary>
/// Renders Data Grid.
/// </summary>
public void RenderDataGrid()
{
pictureBox1.Hide();
// get selectedDate
selectedDate = bookDTPicker.Value.Date;
context = new SembawangSportEntities();
// LINQ doesn't support 'Date' type, so we have to use `int` types
var bookingsByDayQuery = context.Bookings
.Where(x => x.BookingDateFrom.Day == selectedDate.Day)
.Where(x => x.BookingDateFrom.Month == selectedDate.Month)
.Where(x => x.BookingDateFrom.Year == selectedDate.Year)
//.Where(x => x.Facility.FacilityType == "Badminton Court"); // REMOVE: for testing
.Where(x => x.Facility.FacilityType == facilityTypeCombo.Text);
// - Convert it to a List<Booking>
listBookings = bookingsByDayQuery.ToList();
// Generate the appropriate amount of FacilitySchedule objects
// Get get unique facility types (e.g. "Badminton Court 1", "Badminton Court 2", "Badminton Court 3")
var facilityNames = context.Facilities.Where(x => x.FacilityType == facilityTypeCombo.Text);
listFacilityAvailabiltyByDay = new List <FacilitySchedule>();
// create 3 FacilitySchedule objects
foreach (var facility in facilityNames.ToList())
{
listFacilityAvailabiltyByDay.Add(new FacilitySchedule(listBookings, facility.FacilityName));
}
// display it in dataGridView
dataGridView1.DataSource = listFacilityAvailabiltyByDay.ToList();
// resize columns as necessary
dataGridView1.Columns[0].Width = 200;
for (var i = 1; i < dataGridView1.Columns.Count; i++)
{
dataGridView1.Columns[i].Width = 50;
}
// change column header names
dataGridView1.Columns["FacName"].HeaderText = "Facility Name";
int hour;
string am_pm;
for (var i = 1; i <= 14; i++)
{
hour = i + 6;
if (hour >= 7 && hour <= 11)
{
am_pm = "AM";
}
else if (hour == 12)
{
am_pm = "PM";
}
else
{
hour -= 12;
am_pm = "PM";
}
dataGridView1.Columns[i].HeaderText = hour.ToString() + am_pm;
}
}