public List <string> getBreaks(PatientAppPostData user) { using (SqlConnection conn = new SqlConnection(Connection.ConnStr())) { int counter = 0; SqlDataAdapter adapter = new SqlDataAdapter(); MyHashSet <DoctorBreakPatientApp> breaks = new MyHashSet <DoctorBreakPatientApp>(); bool isPatient = false; //да оправя заявката да изкарв заетите часове за съответния ден , почивните часове на доктора за съответния ден, и обичайните почивни часове string Q = "USE Hospitology;" + "SELECT d.start_time ,d.end_time, b.extra_break_start_time,b.extra_break_finish_time," + "b.regular_break_start_time,b.regular_break_finish_time " + "FROM dbo.Doctors_breaks b " + "JOIN dbo.Doctors d ON (d.id_doctor=b.doctor_id) " + "JOIN dbo.Patients p ON (d.id_doctor=p.doctor_id) " + "WHERE d.id_doctor=@DoctorID OR " + "((b.extra_break_start_time BETWEEN @date AND @datePlusOne) OR " + "(b.extra_break_finish_time BETWEEN @date AND @datePlusOne)) OR " + "(( b.regular_break_start_time BETWEEN '2000-10-10' AND '6666-10-10'));"; /*"USE Hospitology;SELECT d.start_time ,d.end_time, b.extra_break_start_time,b.extra_break_finish_time," + * "b.regular_break_start_time,b.regular_break_finish_time,c.appointment_date,p.EGN_patient " + * " FROM dbo.Doctors_breaks b " + * " JOIN dbo.Doctors d ON (d.doctor_username=b.username_doctor) JOIN dbo.Patients p ON (d.doctor_username=p.username_doctor) " + * " JOIN dbo.GP_Appointment_History c ON (d.doctor_username=c.username_doctor) " + * " WHERE d.doctor_username=@DoctorID AND ((b.extra_break_start_time BETWEEN @date AND @datePlusOne) AND (b.extra_break_finish_time BETWEEN @date AND @datePlusOne)) " + * "AND ((c.appointment_date BETWEEN @date AND @datePlusOne)) OR(( b.regular_break_start_time BETWEEN '2000-10-10' AND '6666-10-10'));"; */ adapter.SelectCommand = new SqlCommand(Q, conn); adapter.SelectCommand.Parameters.AddWithValue("@date", user.AppDate); adapter.SelectCommand.Parameters.AddWithValue("@DoctorID", user.DoctorID); adapter.SelectCommand.Parameters.AddWithValue("@datePlusOne", user.AppDatePlusOne); DataTable tb = new DataTable(); adapter.Fill(tb); foreach (DataRow breakItem in tb.Rows) { DoctorBreakPatientApp docBreak = new DoctorBreakPatientApp(); docBreak.startTime = breakItem["start_time"].ToString(); docBreak.endTime = breakItem["end_time"].ToString(); docBreak.regularBreakStart = breakItem["regular_break_start_time"].ToString(); docBreak.regularBreakEnd = breakItem["regular_break_finish_time"].ToString(); docBreak.extraBreakStart = breakItem["extra_break_start_time"].ToString(); docBreak.extraBreakEnd = breakItem["extra_break_finish_time"].ToString(); //docBreak.patientID = breakItem["EGN_patient"].ToString(); //add 20 min for one patient Appointmet TODO VALERIIIIIIIII /// //i have to rapair the query becouse it returns even the wrong appointment dates /*if (user.AppDate != breakItem["appointment_date"].ToString()) * { * docBreak.appointmentStart = null; * } * else * { * docBreak.appointmentStart = breakItem["appointment_date"].ToString(); * } * */ breaks.Add(docBreak); } string secQ = "USE Hospitology;" + "SELECT * FROM GP_Appointment_History WHERE doctor_id=@DoctorID AND appointment_date BETWEEN @date AND @datePlusOne"; adapter.SelectCommand = new SqlCommand(secQ, conn); adapter.SelectCommand.Parameters.AddWithValue("@date", user.AppDate); adapter.SelectCommand.Parameters.AddWithValue("@DoctorID", user.DoctorID); adapter.SelectCommand.Parameters.AddWithValue("@datePlusOne", user.AppDatePlusOne); tb = new DataTable(); adapter.Fill(tb); foreach (DataRow breakItem in tb.Rows) { DoctorBreakPatientApp docBreak = new DoctorBreakPatientApp(); docBreak.appointmentStart = breakItem["appointment_date"].ToString(); docBreak.patientID = breakItem["patient_id"].ToString(); breaks.Add(docBreak); } //i WANT TO RETURN ONLY THE free hours for that Day if there are some //start DateTime workingDayStarts = Convert.ToDateTime(breaks[0].startTime); DateTime workingDayEnd = Convert.ToDateTime(breaks[0].endTime); List <DateTime> dateList = new List <DateTime>(); counter = 0; int dateCompareResult = DateTime.Compare(workingDayStarts, workingDayEnd); //create list of working day scheduale while (dateCompareResult < 1 && dateCompareResult != 0) { if (counter == 0) { dateList.Add(workingDayStarts); ++counter; } else { workingDayStarts = workingDayStarts.AddMinutes(20); dateCompareResult = DateTime.Compare(workingDayStarts, workingDayEnd); if (dateCompareResult == 0) { break; } dateList.Add(workingDayStarts); } } //Doctor regular break start foreach (var item in breaks) { //regular breake Start if (item.regularBreakStart == null || item.regularBreakStart == "") { continue; } DateTime breakeS = Convert.ToDateTime(item.regularBreakStart); int breakeSHours = breakeS.Hour * 60; int breakeSMinutes = breakeS.Minute; int breakeStart = breakeSHours + breakeSMinutes; DateTime breakeE = Convert.ToDateTime(item.regularBreakEnd); int breakeEHours = breakeE.Hour * 60; int breakeEMinutes = breakeE.Minute; int breakeEnd = breakeEHours + breakeEMinutes; for (int i = dateList.Count - 1; i >= 0; i--) { int currentTime = dateList[i].Hour * 60 + dateList[i].Minute; if ((currentTime >= breakeStart) && (breakeEnd > currentTime)) { dateList.RemoveAt(i); } } } //Doctor regular breake END //Doctor regular break start foreach (var item in breaks) { if (item.extraBreakStart == null || item.extraBreakStart == "") { continue; } //regular breake Start DateTime breakeS = Convert.ToDateTime(item.extraBreakStart); int breakeSHours = breakeS.Hour * 60; int breakeSMinutes = breakeS.Minute; int breakeStart = breakeSHours + breakeSMinutes; DateTime breakeE = Convert.ToDateTime(item.extraBreakEnd); int breakeEHours = breakeE.Hour * 60; int breakeEMinutes = breakeE.Minute; int breakeEnd = breakeEHours + breakeEMinutes; for (int i = dateList.Count - 1; i >= 0; i--) { int currentTime = dateList[i].Hour * 60 + dateList[i].Minute; if ((currentTime >= breakeStart) && (breakeEnd > currentTime)) { dateList.RemoveAt(i); } } } //Doctor regular breake END DateTime lastDate = new DateTime(); //Patient Appointment Start foreach (var item in breaks) { if (item.appointmentStart == null || item.appointmentStart == "") { continue; } DateTime appS = Convert.ToDateTime(item.appointmentStart); var date = appS.ToString("yyyy-MM-dd"); if (user.Patient == item.patientID && user.AppDate == date) { isPatient = true; return(new List <string>()); } int appSHours = appS.Hour * 60; int appSMinutes = appS.Minute; int appStart = appSHours + appSMinutes; DateTime appE = appS.AddMinutes(20); int appEHours = appE.Hour * 60; int appEMinutes = appE.Minute; int appEnd = appEHours + appEMinutes; for (int i = dateList.Count - 1; i >= 0; i--) { int currentTime = dateList[i].Hour * 60 + dateList[i].Minute; if ((currentTime >= appStart) && (appEnd > currentTime)) { if (lastDate == dateList[i]) { break; } dateList.RemoveAt(i); lastDate = dateList[i]; } } } //Patient Appointment End List <string> dateStr = new List <string>(); for (int i = 0; i < dateList.Count; i++) { dateStr.Add(dateList[i].ToString("HH:mm")); } //end if (isPatient) { //if the Patient have booked appointment return(new List <string>()); } return(dateStr); } }
public JsonResult getDoctorBreaks(PatientAppPostData user) { List <string> breaks = getBreaks(user); return(this.Json(breaks, JsonRequestBehavior.AllowGet)); }