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")));
            }
        }