/**
* 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);
}