public static DataTable GetDataTable_WorkingStaffOf(int organistion_id, DateTime date)
{
string sql = @"SELECT Distinct
--r.register_staff_id,r.register_staff_date_added,r.organisation_id,r.provider_number AS registration_provider_number,
--r.excl_sun, r.excl_mon, r.excl_tue, r.excl_wed, r.excl_thu, r.excl_fri, r.excl_sat, r.main_provider_for_clinic,
s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number,
s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external,
s.staff_date_added, s.start_date, s.end_date, s.comment,
s.num_days_to_display_on_booking_screen,
s.show_header_on_booking_screen,
s.bk_screen_field_id,
s.bk_screen_show_key,
s.enable_daily_reminder_sms,
s.enable_daily_reminder_email,
s.hide_booking_notes,
" + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @",
t.title_id, t.descr
FROM
RegisterStaff AS r
LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id
LEFT OUTER JOIN Person p ON s.person_id = p.person_id
LEFT OUTER JOIN Title t ON t.title_id = p.title_id
WHERE
s.staff_id > 0 AND s.is_fired = 0 AND r.is_deleted = 0 AND r.organisation_id = " + organistion_id + @"
-- get unavail bookings for whole day .... and make sure there are none
AND (SELECT COUNT(*) FROM Booking
WHERE
(
(booking_type_id = 341 AND (organisation_id IS NULL OR organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') )) OR
(booking_type_id = 342 AND (organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') ))
)
AND
(date_deleted IS NULL)
AND
(
(is_recurring = 0 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) = '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"') OR
(is_recurring = 1 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) <= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"'
AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_end))) >= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"'
AND (Booking.recurring_weekday_id = " + WeekDayDB.GetWeekDayID(date.DayOfWeek) + @"))
)
AND
(provider = r.staff_id) ) = 0
AND (SELECT COUNT(*) FROM Staff WHERE (staff_id = r.staff_id) AND (excl_" + date.DayOfWeek.ToString().Substring(0, 3).ToLower() + @" = 0)) > 0
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
int staff_id = -1;
Staff staff = null;
if (IsValidFormStaffID() && chkOnlyThisProvider.Checked)
{
staff = StaffDB.GetByID(GetFormStaffID());
if (staff == null)
{
throw new CustomMessageException("Invalid url staff");
}
staff_id = staff.StaffID;
}
int org_id = 0;
Organisation org = null;
if (IsValidFormOrgID() && chkOnlyThisOrganistion.Checked)
{
org = OrganisationDB.GetByID(GetFormOrgID());
if (org == null)
{
throw new CustomMessageException("Invalid url org");
}
lblOrganistion.Text = org.Name;
}
int booking_type_id = org_id != 0 ? 341 : 342;
// need to make sure at least one day is selected
if (!chkSunday.Checked && !chkMonday.Checked && !chkTuesday.Checked && !chkWednesday.Checked &&
!chkThursday.Checked && !chkFriday.Checked && !chkSaturday.Checked)
{
throw new CustomMessageException("At least one day must be selected");
}
string days = (chkSunday.Checked ? "1" : "0") + (chkMonday.Checked ? "1" : "0") + (chkTuesday.Checked ? "1" : "0") + (chkWednesday.Checked ? "1" : "0") +
(chkThursday.Checked ? "1" : "0") + (chkFriday.Checked ? "1" : "0") + (chkSaturday.Checked ? "1" : "0");
bool allDay = chkAllDay.Checked;
TimeSpan start_time = allDay ? new TimeSpan(0, 0, 0) : new TimeSpan(Convert.ToInt32(ddlStartHour.SelectedValue), Convert.ToInt32(ddlStartMinute.SelectedValue), 0);
TimeSpan end_time = allDay ? new TimeSpan(23, 59, 0) : new TimeSpan(Convert.ToInt32(ddlEndHour.SelectedValue), Convert.ToInt32(ddlEndMinute.SelectedValue), 0);
if (!allDay && (start_time >= end_time))
{
throw new CustomMessageException("End time must be after start time");
}
// need to check start date and end date are valid dates (make another method to check this) is_valid_date(txt_date)
string start_date_text = txtStartDate.Text;
string end_date_text = txtEndDate.Text;
bool valid_start_date = Regex.IsMatch(start_date_text, @"^\d{2}\-\d{2}\-\d{4}$");
bool valid_end_date = Regex.IsMatch(end_date_text, @"^\d{2}\-\d{2}\-\d{4}$");
if (!valid_start_date)
{
throw new CustomMessageException("Invalid start date - Must be in the format dd-mm-yyyy");
}
if (!valid_end_date)
{
throw new CustomMessageException("Invalid end date - Must be in the format dd-mm-yyyy");
}
DateTime start_datetime = new DateTime(Convert.ToInt32(txtStartDate.Text.Substring(6, 4)), Convert.ToInt32(txtStartDate.Text.Substring(3, 2)), Convert.ToInt32(txtStartDate.Text.Substring(0, 2)));
DateTime end_datetime = end_date_text.Length == 0 ? DateTime.MinValue : new DateTime(Convert.ToInt32(txtEndDate.Text.Substring(6, 4)), Convert.ToInt32(txtEndDate.Text.Substring(3, 2)), Convert.ToInt32(txtEndDate.Text.Substring(0, 2)));
bool same_start_and_end_date = (start_datetime == end_datetime);
int every_n_weeks = Convert.ToInt32(ddlEveryNWeeks.SelectedValue);
// need to check that IF end date not null ... check 3nd date is after first date
if (end_date_text.Length > 0)
{
if (start_datetime > end_datetime)
{
throw new CustomMessageException("End date must be after start date");
}
// add one day to the end date because 7th-8th will want 8th included, so make it 7th 00:00 to 9th 00:00
end_datetime = end_datetime.AddDays(1);
}
if (!same_start_and_end_date && every_n_weeks > 1 && radBookingSequenceTypeSeries.Checked)
{
throw new CustomMessageException("For bookings less frequently than every 1 week, you must select \"Create seperate unavailabilities\"." +
((end_date_text.Length > 0) ? "" : "\r\n" +
"\r\n" +
"You also must set an end date when creating seperate unavailabilities."));
}
if (!same_start_and_end_date && every_n_weeks == 1 && !radBookingSequenceTypeSeperate.Checked && !radBookingSequenceTypeSeries.Checked)
{
throw new CustomMessageException("Please select either \"Create seperate unavailabilities\" or \"Create single series\"" + "\r\n" +
"<small>" +
"Creating seperate unavailabilities - once created, deleting one of those day's unavailability will not remove other unavailabilities" + "\r\n" +
"Creating as a series - once created, deleting any instance of the series will remove all instances of this series" +
"</small>");
}
if (radBookingSequenceTypeSeperate.Checked && end_date_text.Length == 0)
{
throw new CustomMessageException("Can not select \"Create seperate unavailabilities\" without an end date" + "\r\n" +
"\r\n" +
"Either add an end date, or change to \"Create single series\"");
}
bool create_as_series = !same_start_and_end_date && radBookingSequenceTypeSeries.Checked;
if (every_n_weeks > 1)
{
create_as_series = false;
}
int unavailability_reason_id = -1;
if (ddlProvUnavailabilityReason.Visible)
{
unavailability_reason_id = Convert.ToInt32(ddlProvUnavailabilityReason.SelectedValue);
}
if (ddlOrgUnavailabilityReason.Visible)
{
unavailability_reason_id = Convert.ToInt32(ddlOrgUnavailabilityReason.SelectedValue);
}
Booking[] bookings = BookingDB.GetToCheckOverlap_Recurring(start_datetime, end_datetime, start_time, end_time, days, staff, org, booking_type_id == 342, true, false, true);
//if (Booking.HasOverlap(bookings, start_datetime, end_datetime, days, start_time, end_time, null))
// throw new CustomMessageException("Please move or delete existing bookings first.");
Booking[] overlappingBookings = Booking.GetOverlappingBookings(bookings, start_datetime, end_datetime, days, start_time, end_time, every_n_weeks, null);
if (overlappingBookings.Length > 0)
{
string space = " ";
string bookingDates = overlappingBookings.Length == 0 ? string.Empty : "<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">";
for (int i = 0; i < overlappingBookings.Length; i++)
{
string href = overlappingBookings[i].GetBookingSheetLink();
if (href.StartsWith("~/"))
{
href = href.Substring(2);
}
string allFeatures = "dialogWidth:1500px;dialogHeight:1000px;center:yes;resizable:no; scroll:no";
string js = "javascript:window.showModalDialog('" + href + "', '', '" + allFeatures + "');document.getElementById('btnUpdateEPCInfo').click();return false;";
string link = "<a href=\"#\" onclick=\"" + js + "\">" + (overlappingBookings[i].Patient != null ? overlappingBookings[i].Patient.Person.FullnameWithoutMiddlename : overlappingBookings[i].BookingID.ToString()) + "</a>";
bookingDates += "<tr><td>" + space + overlappingBookings[i].DateStart.ToString(@"ddd MMM d, yyy HH:mm") + "</td><td width=\"10\"></td><td>" + link + "</td></tr>";
}
bookingDates += overlappingBookings.Length == 0 ? string.Empty : "</table>";
throw new CustomMessageException("Can not create an unavailability until these existing bookings have been deleted or moved:" + "<br /><small>" + bookingDates + "</small>");
}
// MAKE BOOKING FOR EACH WEEK DAY!
bool madeAtLeastOneBooking = false;
for (int i = 0; i < 7; i++)
{
if (days[i] != '1')
{
continue;
}
DayOfWeek dayOfWeek = WeekDayDB.GetDayOfWeek(i + 1);
if (create_as_series)
{
BookingDB.Insert(start_datetime, end_datetime, org == null ? 0 : org.OrganisationID, staff == null ? -1 : staff.StaffID, -1, -1,
booking_type_id, 0, Convert.ToInt32(unavailability_reason_id), Convert.ToInt32(Session["StaffID"]), 1, Convert.ToInt32(Session["StaffID"]), DateTime.Now, -1, DateTime.MinValue, -1, DateTime.MinValue, false, false, false, true, dayOfWeek, start_time, end_time);
madeAtLeastOneBooking = true;
}
else
{
// get which dates will occur .. and create individual bookings....
DateTime curStartDate = start_datetime;
while (curStartDate.DayOfWeek != dayOfWeek)
{
curStartDate = curStartDate.AddDays(1);
}
DateTime curStartDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, start_time.Hours, start_time.Minutes, 0);
DateTime curEndDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, end_time.Hours, end_time.Minutes, 0);
int weekNbr = 0;
while ((allDay && curStartDateTime.Date < end_datetime.Date) || (!allDay && curStartDateTime.Date <= end_datetime.Date))
{
if (weekNbr % every_n_weeks == 0)
{
BookingDB.Insert(curStartDateTime, curEndDateTime, org == null ? 0 : org.OrganisationID, staff == null ? -1 : staff.StaffID, -1, -1,
booking_type_id, 0, Convert.ToInt32(unavailability_reason_id), Convert.ToInt32(Session["StaffID"]), 1, Convert.ToInt32(Session["StaffID"]), DateTime.Now, -1, DateTime.MinValue, -1, DateTime.MinValue, false, false, false, false, curStartDateTime.DayOfWeek, TimeSpan.Zero, TimeSpan.Zero);
madeAtLeastOneBooking = true;
}
curStartDateTime = curStartDateTime.AddDays(7);
curEndDateTime = curEndDateTime.AddDays(7);
weekNbr++;
}
}
}
if (!madeAtLeastOneBooking)
{
throw new CustomMessageException("No bookings made - please check that the day/s of week selected are within the dates specified.");
}
UpdateList();
// close this window
Page.ClientScript.RegisterStartupScript(this.GetType(), "close", "<script language=javascript>window.returnValue=true;self.close();</script>");
}
catch (CustomMessageException cmEx)
{
SetErrorMessage(cmEx.Message);
}
catch (Exception ex)
{
SetErrorMessage("", ex.ToString());
}
}
protected void AddRecurringBooking()
{
//UrlReturnPage returnPage = GetUrlReturnPage();
bool?checkClashAllOrgs = UrlCheckClashAllOrgs;
DateTime? startDateTime = UrlStartDateTime;
DateTime? endDateTime = UrlEndDateTime;
TimeSpan? startTime = UrlStartTime;
TimeSpan? endTime = UrlEndTime;
int? bookingTypeID = UrlBookingTypeID;
Patient patient = UrlPatient;
Organisation org = UrlOrg;
Staff staff = UrlStaff;
Offering offering = UrlOffering;
bool? confirmed = UrlIsConfirmed;
int? unavailabilityReasonID = UrlUnavailabilityReasonID;
string days = UrlBookingDays;
bool? createAsSeries = UrlCreateAsSeries;
int? everyNWeeks = UrlEveryNWeeks;
if (startDateTime == null)
{
throw new Exception("Invalid url field start_datetime");
}
if (endDateTime == null) // if no end time for recurring booking, url has "NULL" which returns this as DateTime.MinValue (not as null)
{
throw new Exception("Invalid url field end_datetime");
}
if (startTime == null)
{
throw new Exception("Invalid url field start_time");
}
if (endTime == null)
{
throw new Exception("Invalid url field end_time");
}
if (bookingTypeID == null)
{
throw new Exception("Invalid url field booking_type_id");
}
//if (org == null)
// throw new Exception("Invalid url field org_id");
//if (staff == null)
// throw new Exception("Invalid url field staff_id");
//if (offering == null)
// throw new Exception("Invalid url field offering_id");
if (confirmed == null)
{
throw new Exception("Invalid url field is_confirmed");
}
if (unavailabilityReasonID == null)
{
throw new Exception("Invalid url field unavailability_reason_id");
}
if (days == null)
{
throw new Exception("Invalid url field days");
}
if (createAsSeries == null)
{
throw new Exception("Invalid url field create_as_series");
}
if (everyNWeeks == null)
{
throw new Exception("Invalid url field every_n_weeks");
}
if (endTime.Value == new TimeSpan(23, 59, 0))
{
endTime = new TimeSpan(23, 59, 59);
}
int booking_confirmed_by_type_id = !confirmed.Value ? -1 : 1;
int confirmedBy = !confirmed.Value ? -1 : GetStaffID();
DateTime dateConfirmed = !confirmed.Value ? DateTime.MinValue : DateTime.Now;
if (bookingTypeID.Value == 34)
{
// This has not been tested!!!
//throw new Exception();
if (createAsSeries.Value)
{
throw new Exception();
}
// check booking is valid ie no overlapping with current bookings
Booking[] bookings = BookingDB.GetToCheckOverlap_Recurring(startDateTime.Value, endDateTime.Value, startTime.Value, endTime.Value, days, staff, org, false, true, false, true);
Booking[] overlappingBookings = Booking.GetOverlappingBookings(bookings, startDateTime.Value, endDateTime.Value, days, startTime.Value, endTime.Value, everyNWeeks.Value);
if (overlappingBookings.Length > 0)
{
string fromTime = startDateTime.Value.Hour.ToString().PadLeft(2, '0') + ":" + startDateTime.Value.Minute.ToString().PadLeft(2, '0');
string toTime = endDateTime.Value.Hour.ToString().PadLeft(2, '0') + ":" + endDateTime.Value.Minute.ToString().PadLeft(2, '0');
throw new CustomMessageException("Can not book due to overlap with existing booking");
}
// Make booking for each weekday
for (int i = 0; i < 7; i++)
{
if (days[i] != '1')
{
continue;
}
DayOfWeek dayOfWeek = WeekDayDB.GetDayOfWeek(i + 1);
// get which dates will occur, and create individual bookings
DateTime curStartDate = startDateTime.Value;
while (curStartDate.DayOfWeek != dayOfWeek)
{
curStartDate = curStartDate.AddDays(1);
}
DateTime curStartDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, startTime.Value.Hours, startTime.Value.Minutes, 0);
DateTime curEndDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, endTime.Value.Hours, endTime.Value.Minutes, 0);
int weekNbr = 0;
while (curStartDateTime.Date <= endDateTime.Value.Date)
{
if (weekNbr % everyNWeeks.Value == 0)
{
BookingDB.Insert(curStartDateTime, curEndDateTime, org.OrganisationID, staff == null ? -1 : staff.StaffID, patient == null ? -1 : patient.PatientID, offering == null ? -1 : offering.OfferingID,
bookingTypeID.Value, 0, unavailabilityReasonID == null ? -1 : unavailabilityReasonID.Value, GetStaffID(), booking_confirmed_by_type_id, confirmedBy, dateConfirmed, -1, DateTime.MinValue, -1, DateTime.MinValue, false, false, false, false, curStartDateTime.DayOfWeek, TimeSpan.Zero, TimeSpan.Zero);
}
curStartDateTime = curStartDateTime.AddDays(7);
curEndDateTime = curEndDateTime.AddDays(7);
weekNbr++;
}
}
}
else
{
// check booking is valid ie no overlapping with current bookings
Booking[] bookings = BookingDB.GetToCheckOverlap_Recurring(startDateTime.Value, endDateTime.Value, startTime.Value, endTime.Value, days, staff, checkClashAllOrgs.Value && (bookingTypeID.Value != 342 && bookingTypeID.Value != 341) ? null : org, bookingTypeID.Value == 342 || bookingTypeID.Value == 341, bookingTypeID.Value != 341, false, true);
Booking clash = Booking.HasOverlap(bookings, startDateTime.Value, endDateTime.Value, days, startTime.Value, endTime.Value, everyNWeeks.Value);
if (clash != null)
{
string fromTime = startDateTime.Value.Hour.ToString().PadLeft(2, '0') + ":" + startDateTime.Value.Minute.ToString().PadLeft(2, '0');
string toTime = endDateTime.Value.Hour.ToString().PadLeft(2, '0') + ":" + endDateTime.Value.Minute.ToString().PadLeft(2, '0');
bool isFullDay = startDateTime.Value.Hour == 0 && startDateTime.Value.Minute == 0 && endDateTime.Value.Hour == 0 && endDateTime.Value.Minute == 0;
throw new CustomMessageException("Can not book " + startDateTime.Value.ToString(@"ddd MMM d") + (isFullDay ? "" : " " + fromTime + "-" + toTime) + " due to overlap with existing booking on " + clash.DateStart.ToString(@"ddd MMM d ") + clash.DateStart.ToString("h:mm") + (clash.DateStart.Hour < 12 ? "am" : "pm"));
}
// Make booking for each weekday
for (int i = 0; i < 7; i++)
{
if (days[i] != '1')
{
continue;
}
DayOfWeek dayOfWeek = WeekDayDB.GetDayOfWeek(i + 1);
if (createAsSeries.Value)
{
DateTime dateStart = startDateTime.Value;
while (dateStart.DayOfWeek != dayOfWeek)
{
dateStart = dateStart.AddDays(1);
}
DateTime dateEnd = endDateTime.Value;
while (dateEnd != DateTime.MinValue && dateEnd.DayOfWeek != dayOfWeek)
{
dateEnd = dateEnd.AddDays(-1);
}
BookingDB.Insert(dateStart, dateEnd, org == null || bookingTypeID.Value == 342 ? 0 : org.OrganisationID, staff == null ? -1 : staff.StaffID, patient == null ? -1 : patient.PatientID, offering == null ? -1 : offering.OfferingID,
bookingTypeID.Value, 0, unavailabilityReasonID.Value, GetStaffID(), booking_confirmed_by_type_id, confirmedBy, dateConfirmed, -1, DateTime.MinValue, -1, DateTime.MinValue, false, false, false, true, dayOfWeek, startTime.Value, endTime.Value);
}
else
{
// get which dates will occur, and create individual bookings
DateTime curStartDate = startDateTime.Value;
while (curStartDate.DayOfWeek != dayOfWeek)
{
curStartDate = curStartDate.AddDays(1);
}
DateTime curStartDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, startTime.Value.Hours, startTime.Value.Minutes, 0);
DateTime curEndDateTime = new DateTime(curStartDate.Year, curStartDate.Month, curStartDate.Day, endTime.Value.Hours, endTime.Value.Minutes, 0);
int weekNbr = 0;
while (curStartDateTime.Date <= endDateTime.Value.Date)
{
if (weekNbr % everyNWeeks.Value == 0)
{
BookingDB.Insert(curStartDateTime, curEndDateTime, org == null || bookingTypeID.Value == 342 ? 0 : org.OrganisationID, staff == null ? -1 : staff.StaffID, patient == null ? -1 : patient.PatientID, offering == null ? -1 : offering.OfferingID,
bookingTypeID.Value, 0, unavailabilityReasonID.Value, GetStaffID(), booking_confirmed_by_type_id, confirmedBy, dateConfirmed, -1, DateTime.MinValue, -1, DateTime.MinValue, false, false, false, false, curStartDateTime.DayOfWeek, TimeSpan.Zero, TimeSpan.Zero);
}
curStartDateTime = curStartDateTime.AddDays(7);
curEndDateTime = curEndDateTime.AddDays(7);
weekNbr++;
}
}
}
}
}
