public DataSet LoadAppointment(Date date, Time time, string stffID)
{
Time minus = time;
minus.Minutes = minus.Minutes - 30;
Time plus = time;
plus.Minutes = plus.Minutes + 30;
DataSet ds = new DataSet();
string sql;
sql = @"SELECT StaffID FROM Appointments WHERE AppointmentDate = '" + date.ToString() + "' AND StaffId = '" + stffID + "' AND AppointmentTime >= '" + minus.ToString() + "' AND AppointmentTime <= '" + plus.ToString() + "' INTERSECT SELECT StaffID FROM Shifts WHERE StartTime <= '" + time.ToString() + "' AND FinishTime >= '" + time.ToString() + "' AND StaffId = '" + stffID + "' AND Date = '" + date.ToString() + "'";
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Info, new Message("SQL= " + sql)));
try
{
ds = DBManager.getDBConnectionInstance().getDataSet(sql);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return(ds);
}
public DataSet LoadAppointment(Date date, string stffID)
{
string sql;
Time now = new Time(DateTime.Now.ToString("HH_mm"));
if (date.ToString() != DateTime.Now.ToString("yyyy_MM_dd"))
{
sql = @"SELECT * FROM Appointments WHERE AppointmentDate = '" + date.ToString() + "' AND StaffId = '" + stffID + "'";
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Info, new Message("SQL= " + sql)));
}
else
{
now.Minutes = now.Minutes - 30;
sql = @"SELECT * FROM Appointments WHERE AppointmentDate = '" + date.ToString() + "' AND StaffId = '" + stffID + "' AND AppointmentTime >= '" + now.ToString() + "'";
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Info, new Message("SQL= " + sql)));
}
return(DBManager.getDBConnectionInstance().getDataSet(sql));
}
public void OnlyDateInsert(string stffID, string appointmentDate)
{
try
{
string appointmentTime = "";
NewAppointment = null;
bool check = false;
List <Appointment> temp = new List <Appointment>();
Date reqDate = new Date(appointmentDate);
Time nowTime = new Time(DateTime.Now.ToString("HH_mm"));
Date nowDate = new Date(DateTime.Now.ToString("yyyy_MM_dd"));
DataSet shiftDs = LoadShifts(reqDate, stffID);
Time startLook = new Time(shiftDs.Tables[0].Rows[0][2].ToString());
Time endLook = new Time(shiftDs.Tables[0].Rows[0][3].ToString());
if (nowDate.Raw == reqDate.Raw)
{
if (nowTime.Raw > startLook.Raw && nowTime.Raw < endLook.Raw)
{
startLook = nowTime;
}
else if (nowTime.Raw > endLook.Raw)
{
startLook = endLook;
}
}
DataSet appointmentsDs = LoadAppointment(new Date(appointmentDate), stffID);
if (appointmentsDs.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in appointmentsDs.Tables[0].Rows)
{
temp.Add(new Appointment(dr));
}
temp.OrderBy(x => x.Time.Raw);
for (int i = 0; i <= temp.Count; i++)
{
if (i == 0)
{
int mydif = temp[i].Time - startLook;
if (mydif >= 30)//we just need to fit the new appointment
{
appointmentTime = startLook.ToString();
NewAppointment = new Appointment(activePatient.PatientId, stffID, appointmentDate, appointmentTime);
check = true;
i = temp.Count;
}
}
else if (i == temp.Count)
{
int mydif = endLook - temp[i - 1].Time;
if (mydif >= 60)//we need to fit the appointment at temp[i].Time and the new one
{
appointmentTime = (temp[i - 1].Time + (int)30).ToString();
NewAppointment = new Appointment(activePatient.PatientId, stffID, appointmentDate, appointmentTime);
check = true;
}
}
else
{
int dif = temp[i].Time - temp[i - 1].Time;
if (dif >= 60)//30+30 in to fit both the new appointment and the one that has a start time at at tem[i].Time
{
appointmentTime = (temp[i - 1].Time + (int)30).ToString();
NewAppointment = new Appointment(activePatient.PatientId, stffID, appointmentDate, appointmentTime);
check = true;
i = temp.Count;
}
}
}
}
else
{
NewAppointment = new Appointment(activePatient.PatientId, stffID, appointmentDate, startLook.ToString());
check = true;
}
if (check == false)
{
MessageBox.Show("Chosen day is completelly booked");
}
else
{
DialogResult dl = MessageBox.Show(String.Format
("You have not selected a time, therefore you appointment will be booked for the first available timeslot, which is:{0}{1}.",
Environment.NewLine, NewAppointment.AppointmentTime), "Attention!", MessageBoxButtons.YesNo);
if (dl == DialogResult.Yes)
{
InsertFull(appointmentDate, NewAppointment.AppointmentTime, stffID, activePatient.PatientId);
MessageBox.Show("Appointment booked successfully!");
Form.ActiveForm.Close();
}
else
{
Form.ActiveForm.Close();
}
}
}
catch (Exception e)
{
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Exception, new Message(e, "Requesting to book by date only bug")));
}
}
public DataSet LoadShiftsByDateTimeID(Date selectedDate, Time selectedTime, string ID)
{
Time t = selectedTime; //TO DO: Read the duration of each appointment from the file to allow non constant durations
t.Minutes += 29;
string selectedTimePlusDuration = t.ToString();
DataSet ds = new DataSet();
try
{
string sql = @"SELECT StaffID FROM Shifts WHERE Date = '" + selectedDate.ToString() + "' AND StartTime <= '" + selectedTime.ToString() + "' AND FinishTime >= '"
+ selectedTimePlusDuration + "' AND StaffID = '" + ID + "'";
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Info, new Message("SQL= " + sql)));
ds = DBManager.getDBConnectionInstance().getDataSet(sql);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return(ds);
}
public void OnlyTimeInsert(string stffID, Time appointmentTime, Date appointmentDate)
{
try
{
int counter = 0;
bool found = false;
newAppointment = null;
List <Appointment> temp = new List <Appointment>();
DataSet ds = new DataSet();
do
{
counter++;
ds = LoadAppointment(appointmentDate, appointmentTime, stffID);
if (ds.Tables[0].Rows.Count > 0)
{
appointmentDate.Day = appointmentDate.Day + 1;
}
else
{
if (Utility.CheckFind(LoadShiftsByDateTimeID(appointmentDate, appointmentTime, stffID)))
{
counter = 15;
found = true;
}
else
{
appointmentDate.Day = appointmentDate.Day + 1;
}
}
}while (counter < 15);
if (found == false)
{
MessageBox.Show("Chosen time is completelly booked over the next 2 weeks");
}
else
{
DialogResult dl = MessageBox.Show(String.Format
("You have not selected a time, therefore you appointment will be booked for the first available day, which is:{0}{1}.",
Environment.NewLine, appointmentDate.ToString()), "Attention!", MessageBoxButtons.YesNo);
if (dl == DialogResult.Yes)
{
try
{
InsertFull(appointmentDate.ToString(), appointmentTime.ToString(), stffID, activePatient.PatientId);
MessageBox.Show("Appointment booked successfully!");
NewAppointment = new Appointment(ActivePatient.PatientId, stffID, appointmentDate.ToString(), appointmentTime.ToString());
Form.ActiveForm.Close();
}
catch (Exception e)
{
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Exception, new Message(e, "Trying to find appointment slot, and save it to the db.")));
throw;
}
}
else
{
Form.ActiveForm.Close();
}
}
}
catch (Exception e)
{
Logger.Instance.WriteLog(new Logger.Logg(Logger.Type.Exception, new Message(e, "Requesting to book by time only bug")));
}
}