/** * Uses and in-line SQL statement to create an OfficeHoursClass object with a given * appointment date * * parameters: DateTime Appointment Date * returns: OfficeHoursClass object */ public static OfficeHoursClass QueryDatabaseForOfficeHours(DateTime dtAppointmentDate) { OfficeHoursClass ohcReturnOfficeHours = null; String strReturnDayOfWeek; DateTime dtDate; TimeSpan dtTimeOpen; TimeSpan dtTimeClose; System.Data.SqlTypes.SqlChars chrStatus; using (SqlConnection connection = getConnection()) { connection.Open(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT TOP 1 * "); sb.Append("FROM OfficeHours O "); sb.Append($"WHERE O.Date = '{dtAppointmentDate}' "); String sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { strReturnDayOfWeek = reader.GetString(0); dtDate = reader.GetDateTime(1); dtTimeOpen = reader.GetTimeSpan(2); dtTimeClose = reader.GetTimeSpan(3); chrStatus = reader.GetSqlChars(4); ohcReturnOfficeHours = new OfficeHoursClass(strReturnDayOfWeek, dtDate, dtTimeOpen, dtTimeClose, chrStatus); return(ohcReturnOfficeHours); } } } } return(ohcReturnOfficeHours); }
/*returns an int pertaining to the succcess or failure of a Doctor requesting a specific day off * 1 = Day Taken off Successfully * -1 = Day requested is in the past * -2 = building is closed on that day * -3 = day requested is within 7 days of the present * * Parameters: a date, and a dentist type UserClass * @author: Tim Olson */ public static int requestDayOff_Doctor(DateTime dtdate, UserClass ucUser) { //Error checking for if the day requested is in the past if (dtdate.Date < DateTime.Today) { return(-1); } //Error checking for if the day requested is a day the office is already closed OfficeHoursClass ohcDay = QueryDatabaseForOfficeHours(dtdate); if (ohcDay == null) { return(-2); } //Error checking for if the date requested is within 7 days of the present if ((dtdate - DateTime.Today).TotalDays < 7) { return(-3); } List <AppointmentClass> appts = getAppointments_Admin(ucUser.m_strUsername, dtdate); foreach (AppointmentClass a in appts) { updateAppointmentStatus(a, 'C'); } for (int i = 0; i < 8; i++) { createAppointment(dtdate.AddHours(9 + i), "Vacation", ucUser.m_strUsername, "Vacation", "Vacation", DateTime.Today); } return(1); }
/* * Returns an integer corresponding to success or failure of appointment creation * 1 = appointment added successfully * -1 = the given appointment date is in the past * -2 = The office is closed that day * -3 = the office is open that day but closed during that time * -4 = You already have an appointment during that time * -5 = The requested dentist is unavailable * -6 = The appointment was within 24 hours of createdDate */ public static int createAppointment(DateTime dtDateTime, String strPatientUserName, String strDentistUserName, String strAppointmentType, String strDescription, DateTime dtCreatedDate) { //Error checking for scheduling in the past if (dtDateTime < DateTime.Now) { return(-1); } //Error checking for scheduling not during Office hours OfficeHoursClass ohcDayOfAppointment = QueryDatabaseForOfficeHours(dtDateTime); if (ohcDayOfAppointment == null) { return(-2); } //Error checking for scheduling during a time when the office is closed /* * if (dtDateTime < ohcDayOfAppointment.m_dtOpenTime || dtDateTime > ohcDayOfAppointment.m_dtCloseTime) * { * return -3; * } */ //Error checking for if patient has another appointment at that time List <AppointmentClass> lsacPatientAppts = getAppointmentsWithCustomerName(strPatientUserName); foreach (AppointmentClass appointment in lsacPatientAppts) { if (appointment.m_dtDateTime == dtDateTime) { return(-4); } } //Error checking for if the dentist has another appointment at that time List <AppointmentClass> lsacDentistAppts = getAppointmentsWithDentistName(strDentistUserName); foreach (AppointmentClass appointment in lsacDentistAppts) { if (appointment.m_dtDateTime == dtDateTime && appointment.m_chrStatus[0] == 'A') { return(-5); } } //error checking for if appointment is being scheduled for within the next 24 hours if (dtDateTime < DateTimeOffset.Now.AddDays(1)) { return(-6); } //validate appointment time (make Office Hours class and query database for hours info) using (SqlConnection connection = getConnection()) { connection.Open(); String sql = ""; SqlCommand command = new SqlCommand(sql, connection); } //add appointment to database Boolean blnWasAppointmentCreated = false; int intNumberOfRowsAffected = 0; using (SqlConnection connection = getConnection()) { connection.Open(); StringBuilder sb = new StringBuilder(); sb.Append("Insert into Appointments Values("); sb.Append($"'{strPatientUserName}', "); sb.Append($"'{strDentistUserName}', "); sb.Append($"'{strAppointmentType}', "); sb.Append($"'{strDescription}', "); sb.Append($"'{dtCreatedDate}', "); sb.Append($"'{dtDateTime}', 'A')"); String sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { intNumberOfRowsAffected = command.ExecuteNonQuery(); if (intNumberOfRowsAffected > 0) { blnWasAppointmentCreated = true; } } } //return whether the add was successful if (blnWasAppointmentCreated) { return(1); } return(-4); }