public static void UpdateStatus(int Calendar_id, int Status_id)
{
string querry = "USE[db_Clinic] UPDATE[dbo].[tbl_Calendar] SET[Status_id] = " + Status_id + " WHERE Calendar_id = " + Calendar_id;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void UpdateTerm(int CalendarDayId, string date, int TermId)
{
string querry = "Use [db_Clinic] UPDATE [dbo].[tbl_Term] SET Calendar_day_id=" + CalendarDayId + ", Date = N'" + date + "' WHERE Term_id = " + TermId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void UpdateCalendarDay(int day, int Calendar_day_id, TimeSpan start, TimeSpan end)
{
string querry = "USE [db_Clinic] UPDATE [dbo].[tbl_Calendar_Day] SET Day=" + day + ", Start_time='" + start + "', End_time='" + end + "' WHERE Calendar_day_id=" + Calendar_day_id;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void CreateCalendarDays(string days)
{
string querry = "USE [db_Clinic] INSERT INTO [dbo].[tbl_Calendar_Day](Day, Start_time, End_time, Calendar_id) VALUES " + days;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
/*public List<ClassDoctor> ListOfDoctorsForSpecifiedCalendar(int calendarId)
* {
* string querry = "USE [db_Clinic] SELECT [tbl_Doctor].[Doctor_id],[Name],[Surname],[Phone_number],[Active],[Degree],[Type_of_specialization],[Office_id],[Specialization], " +
* "[tbl_Degree_of_doctor].[Degree_of_doctor_id],[tbl_Type_of_specialization].[Type_of_specialization_id],[tbl_Specialization].[Specialization_id], [tbl_Calendar_doctor].[Status_id], " +
* "[tbl_Calendar].[Calendar_id], [tbl_Status].[Status] " +
* "FROM[dbo].[tbl_Doctor], [dbo].[tbl_Degree_of_doctor], [dbo].[tbl_Specialization],[dbo].[tbl_Type_of_specialization], [dbo].[tbl_Calendar_doctor], [dbo].[tbl_Calendar], [dbo].[tbl_Status], [dbo].[tbl_Employee] " +
* "WHERE[tbl_Doctor].[Degree_of_doctor_id] =[tbl_Degree_of_doctor].[Degree_of_doctor_id] " +
* "AND[tbl_Doctor].[Type_of_specialization_id] =[tbl_Type_of_specialization].[Type_of_specialization_id] " +
* "AND[tbl_Type_of_specialization].[Specialization_id] =[tbl_Specialization].[Specialization_id] " +
* "AND[Active] = 'Yes' " +
* "AND[tbl_Calendar_doctor].[Doctor_id] =[tbl_Doctor].[Doctor_id] " +
* "AND[tbl_Calendar].[Calendar_id] = " + calendarId +
* " AND [tbl_Status].[Status_id]=[tbl_Calendar_doctor].[Status_id] " +
* "AND [tbl_Calendar].[Calendar_id]=[tbl_Calendar_doctor].[Calendar_id] " +
* "AND [tbl_Employee].[Employee_id]=[tbl_doctor].[Employee_id]";
* ClassQuerry q = new ClassQuerry();
* SqlDataReader dr = q.ExecuteQuerry(querry);
* List<ClassDoctor> DoctorList = new List<ClassDoctor>();
* while (dr.Read())
* {
* ClassDoctor dct = new ClassDoctor();
* dct.Doctor_id = dr.GetInt32("Doctor_id");
* dct.Name = dr.GetString("Name");
* dct.Surname = dr.GetString("Surname");
* dct.PhoneNumber = dr.GetString("Phone_number");
* dct.OfficeNumber = dr.GetInt32("Office_id");
* dct.Active = false;
* if (dr.GetString("Active").Equals("Yes")) dct.Active = true;
*
* ClassDegreeOfDoctor degree = new ClassDegreeOfDoctor();
* degree.DegreeOfDoctorId = dr.GetInt32("Degree_of_doctor_id");
* degree.Degree = dr.GetString("Degree");
* dct.Degree = degree;
*
* ClassTypeOfSpecialization typeOfSpecialization = new ClassTypeOfSpecialization();
* typeOfSpecialization.TypeOfSpecializationId = dr.GetInt32("Type_of_specialization_id");
* typeOfSpecialization.TypeOfSpecialization = dr.GetString("Type_of_specialization");
* dct.TypeOfSpecialization = typeOfSpecialization;
*
* ClassSpecialization specialization = new ClassSpecialization();
* specialization.SpecializationId = dr.GetInt32("Specialization_id");
* specialization.Specialization = dr.GetString("Specialization");
* dct.Specialization = specialization;
* dct.Status = new ClassStatus();
* dct.Status.StatusId = dr.GetInt32("Status_id");
* dct.Status.Status = dr.GetString("Status");
* DoctorList.Add(dct);
* }
* q.CloseConnection();
* return DoctorList;
* }*/
public List <ClassTerm> TermLisTSelectedDoctor(int Calendar_id, int Doctor_id)
{
string querry = "" +
"USE[db_Clinic] " +
"Select Start_time, End_time, Date, Office_number, Name, Surname, tbl_Doctor.Doctor_id " +
"FROM tbl_Term, tbl_Office, tbl_Doctor, tbl_Employee, tbl_Calendar_doctor, tbl_Calendar " +
"WHERE tbl_Office.Office_id = tbl_Term.Office_id " +
"AND tbl_Doctor.Doctor_id = tbl_Term.Doctor_id " +
"AND tbl_Employee.Employee_id = tbl_Doctor.Employee_id " +
"AND tbl_Calendar_doctor.Doctor_id = tbl_Doctor.Doctor_id " +
"AND tbl_Calendar.Calendar_id = tbl_Calendar_doctor.Calendar_id " +
"AND tbl_Doctor.Doctor_id = " + Doctor_id +
" AND tbl_Calendar.Calendar_id = " + Calendar_id;
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
List <ClassTerm> TermList = new List <ClassTerm>();
while (dr.Read())
{
ClassTerm term = new ClassTerm();
term.StartTime = dr.GetTimeSpan(0);
term.EndTime = dr.GetTimeSpan(1);
term.Date = dr.GetDateTime("Date");
term.Doctor = new ClassDoctor();
term.Doctor.Name = dr.GetString("Name");
term.Doctor.Surname = dr.GetString("Surname");
TermList.Add(term);
}
q.CloseConnection();
return(TermList);
}
public static void DeleteCalendarDay(int calendarId)
{
string querry = "USE [db_Clinic] DELETE [dbo].[tbl_Calendar_Day] WHERE Calendar_day_id=" + calendarId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public List <ClassCalendarDoctor> ListOfCalendarDoctor(int calendarId)
{
string querry = "USE[db_Clinic] " +
"SELECT[Calendar_doctor_id],[Name],[Surname],[Status],[Calendar_id], [tbl_Status].[Status_id],[tbl_Calendar_doctor].[Doctor_id] " +
"FROM[dbo].[tbl_Calendar_doctor], [tbl_Status], [tbl_Doctor], [tbl_Employee] " +
"WHERE[tbl_Status].[Status_id] =[tbl_Calendar_doctor].[Status_id] " +
"AND[tbl_Doctor].[Doctor_id] =[tbl_Calendar_doctor].[Doctor_id] " +
"AND[tbl_Employee].[Employee_id]=[tbl_Doctor].[Employee_id] " +
"AND[Calendar_id] = " + calendarId;
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
List <ClassCalendarDoctor> calendarDoctorList = new List <ClassCalendarDoctor>();
while (dr.Read())
{
ClassCalendarDoctor cd = new ClassCalendarDoctor();
cd.CalendarDoctorId = dr.GetInt32("Calendar_doctor_id");
cd.Doctor = new ClassDoctor();
cd.Doctor.Name = dr.GetString("Name");
cd.Doctor.Surname = dr.GetString("Surname");
cd.Doctor.Doctor_id = dr.GetInt32("Doctor_id");
cd.Status = new ClassStatus();
cd.Status.Status = dr.GetString("Status");
cd.Status.StatusId = dr.GetInt32("Status_id");
calendarDoctorList.Add(cd);
}
q.CloseConnection();
return(calendarDoctorList);
}
public List <ClassCalendarDay> ListOfCalendarDays(int calendarId)
{
string querry = "USE[db_Clinic] SELECT[Calendar_day_id],[Day],[Start_time],[End_time],[tbl_Calendar].[Calendar_id], [Year],[Month] " +
"FROM[dbo].[tbl_Calendar_Day], [dbo].[tbl_Calendar] " +
"WHERE[tbl_Calendar].[Calendar_id] =[tbl_Calendar_Day].[Calendar_id] " +
"AND[tbl_Calendar].Calendar_id = " + calendarId;
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
List <ClassCalendarDay> dayList = new List <ClassCalendarDay>();
while (dr.Read())
{
ClassCalendarDay day = new ClassCalendarDay();
day.CalendarDayId = dr.GetInt32("Calendar_day_id");
day.Day = dr.GetInt32("Day");
day.StartTime = dr.GetTimeSpan(2);
day.EndTime = dr.GetTimeSpan(3);
day.Calendar = new ClassCalendar();
day.Calendar.CalendarId = dr.GetInt32("Calendar_id");
day.Calendar.Year = dr.GetInt32("Year");
day.Calendar.Month = dr.GetInt32("Month");
dayList.Add(day);
}
q.CloseConnection();
return(dayList);
}
public List <ClassOffice> ListOfAvailableOfficeForSelectedDay(int Calendar_day_id)
{
string querry = "" +
"SELECT * FROM tbl_Office " +
"WHERE Office_id NOT IN " +
"( " +
"SELECT tbl_Office.Office_id FROM tbl_Calendar_Day, tbl_Term, tbl_Office " +
"WHERE tbl_Calendar_Day.Calendar_day_id = tbl_Term.Calendar_day_id " +
"AND tbl_Calendar_Day.Calendar_day_id = " + Calendar_day_id +
" AND tbl_Office.Office_id = tbl_Term.Office_id " +
")";
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
List <ClassOffice> list = new List <ClassOffice>();
while (dr.Read())
{
ClassOffice office = new ClassOffice();
office.OfficeId = dr.GetInt32("Office_id");
office.OfficeNumber = dr.GetInt16("Office_number");
list.Add(office);
}
q.CloseConnection();
return(list);
}
public void UpdateCalendar(int Calendar_id)
{
string querry = "USE[db_Clinic] " +
"BEGIN " +
"DECLARE @nubmerOfGeneralCalendar int " +
"SELECT @nubmerOfGeneralCalendar = COUNT(*) FROM[dbo].[tbl_Calendar], [dbo].[tbl_Calendar_Doctor], [dbo].[tbl_Status] " +
" WHERE[tbl_Calendar].[Calendar_id] =[tbl_Calendar_Doctor].[Calendar_id] " +
" AND[tbl_Status].Status_id =[tbl_Calendar_Doctor].[Status_id] " +
" AND[tbl_Calendar].[Calendar_id] = " + Calendar_id +
" PRINT @nubmerOfGeneralCalendar; " +
"DECLARE @nubmerOfAcceptedCalendar int " +
"SELECT @nubmerOfAcceptedCalendar = COUNT(*) FROM[dbo].[tbl_Calendar], [dbo].[tbl_Calendar_Doctor], [dbo].[tbl_Status] " +
"WHERE[tbl_Calendar].[Calendar_id] =[tbl_Calendar_Doctor].[Calendar_id] " +
"AND[tbl_Status].Status_id =[tbl_Calendar_Doctor].[Status_id] " +
" AND[tbl_Calendar].[Calendar_id] = " + Calendar_id +
" AND[tbl_Status].Status = 'Accepted by the doctor' " +
"PRINT @nubmerOfAcceptedCalendar; " +
"PRINT @nubmerOfAcceptedCalendar " +
"PRINT @nubmerOfGeneralCalendar " +
"IF(@nubmerOfAcceptedCalendar = @nubmerOfGeneralCalendar) " +
"UPDATE[dbo].[tbl_Calendar] SET[Status_id] = 3 WHERE[Calendar_id] = " + Calendar_id +
" END ";
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
q.CloseConnection();
}
public static void UpdateAppointment(int appointmentId, string description, string topic)
{
string querry = "Use [db_Clinic] UPDATE [dbo].[tbl_Appointment] SET add_description = N'" + description + "', Topic = '" + topic + "' WHERE Appointment_id = " + appointmentId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void DeleteTerm(int TermId)
{
string querry = "Use [db_Clinic] DELETE [dbo].[tbl_Term] WHERE Term_id = " + TermId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void UpdateTerm(int TermId, TimeSpan startTime, TimeSpan endTime)
{
string querry = "Use [db_Clinic] UPDATE [dbo].[tbl_Term] SET Start_time = '" + startTime.ToString() + "', End_time = '" + endTime.ToString() + "' WHERE Term_id = " + TermId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void CreateTerm(TimeSpan startTime, TimeSpan endTime, int CalendarDoctorId, int CalendarDayId, int OfficeId, int doctorID, string Date)
{
string querry = "Use [db_Clinic] INSERT INTO [dbo].[tbl_Term] (Start_time,End_time,Date,Calendar_doctor_id,Calendar_day_id,Office_id,Doctor_id)" +
String.Format("Values ('{0}','{1}','{2}',{3},{4},{5},{6})", startTime, endTime, Date, CalendarDoctorId, CalendarDayId, OfficeId, doctorID);
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void CreateCalendarDay(int day, int calendar_id, TimeSpan start, TimeSpan end)
{
string querry = "USE [db_Clinic] INSERT INTO [dbo].[tbl_Calendar_Day](Day, Start_time, End_time, Calendar_id) " +
String.Format("Values({0}, '{1}', '{2}', {3})", day, start, end, calendar_id);
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static void CreateTerm(int CalendarDoctorId, int CalendarDayId, int OfficeId, int doctorID, DateTime Date)
{
string querry = "Use [db_Clinic] INSERT INTO [dbo].[tbl_Term] (Start_time,End_time,Date,Calendar_doctor_id,Calendar_day_id,Office_id,Doctor_id)" +
String.Format("Values ('{0}','{1}','{2}',{3},{4},{5},{6})", new TimeSpan(7, 0, 0), new TimeSpan(20, 0, 0), String.Format(Date.Year + "-" + Date.Month + "-" + Date.Day), CalendarDoctorId, CalendarDayId, OfficeId, doctorID);
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public void UpdateCalendarStatus(int Status_id, int Calendar_id)
{
string querry = "USE [db_Clinic] UPDATE [dbo].[tbl_Calendar] SET [Status_id]=" + Status_id + " WHERE [Calendar_id]=" + Calendar_id;
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
q.CloseConnection();
}
public static void DeleteCalendarAndApropriateCalendarDays(int Calendar_id)
{
string querry = "" +
"DELETE FROM tbl_Calendar_Day WHERE Calendar_id = " + Calendar_id +
" DELETE FROM tbl_Calendar WHERE Calendar_id = " + Calendar_id;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static int CreateCalendar(int Year, int Month)
{
string querry = "USE [db_Clinic] INSERT INTO [dbo].[tbl_Calendar](Year,Month,Status_id) " +
String.Format("VALUES({0}, {1}, {2})", Year, Month, ClassSqlCalendar.SelectStatusId(EnumStatus.New));
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
return(SelectCalendarId(Year, Month));
}
public static void UpdateOffice(int termid, int officeId)
{
string querry = "UPDATE tbl_Term " +
"SET Office_id = " + officeId +
" WHERE Term_id=" + termid;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static List <ClassDoctor> ListOfDoctorsForSpecifiedCalendar(int calendarId)
{
string querry = "USE [db_Clinic] SELECT [tbl_Doctor].[Doctor_id],[Name],[Surname],[Phone_number],[Active],[Degree],[Type_of_specialization],[Office_id],[Specialization], " +
"[tbl_Degree_of_doctor].[Degree_of_doctor_id],[tbl_Type_of_specialization].[Type_of_specialization_id],[tbl_Specialization].[Specialization_id], [tbl_Calendar_doctor].[Status_id], " +
"[tbl_Calendar].[Calendar_id], [tbl_Status].[Status] " +
"FROM[dbo].[tbl_Doctor], [dbo].[tbl_Degree_of_doctor], [dbo].[tbl_Specialization],[dbo].[tbl_Type_of_specialization], [dbo].[tbl_Calendar_doctor], [dbo].[tbl_Calendar], [dbo].[tbl_Status], [dbo].[tbl_Employee] " +
"WHERE[tbl_Doctor].[Degree_of_doctor_id] =[tbl_Degree_of_doctor].[Degree_of_doctor_id] " +
"AND[tbl_Doctor].[Type_of_specialization_id] =[tbl_Type_of_specialization].[Type_of_specialization_id] " +
"AND[tbl_Type_of_specialization].[Specialization_id] =[tbl_Specialization].[Specialization_id] " +
"AND[Active] = 'Yes' " +
"AND[tbl_Calendar_doctor].[Doctor_id] =[tbl_Doctor].[Doctor_id] " +
"AND[tbl_Calendar].[Calendar_id] = " + calendarId +
" AND [tbl_Status].[Status_id]=[tbl_Calendar_doctor].[Status_id] " +
"AND [tbl_Calendar].[Calendar_id]=[tbl_Calendar_doctor].[Calendar_id] " +
"AND [tbl_Employee].[Employee_id]=[tbl_doctor].[Employee_id]";
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassDoctor> DoctorList = new List <ClassDoctor>();
while (dr.Read())
{
ClassDoctor dct = new ClassDoctor();
dct.Doctor_id = dr.GetInt32("Doctor_id");
dct.Name = dr.GetString("Name");
dct.Surname = dr.GetString("Surname");
dct.PhoneNumber = dr.GetString("Phone_number");
dct.OfficeNumber = dr.GetInt32("Office_id");
dct.Active = false;
if (dr.GetString("Active").Equals("Yes"))
{
dct.Active = true;
}
ClassDegreeOfDoctor degree = new ClassDegreeOfDoctor();
degree.DegreeOfDoctorId = dr.GetInt32("Degree_of_doctor_id");
degree.Degree = dr.GetString("Degree");
dct.Degree = degree;
ClassTypeOfSpecialization typeOfSpecialization = new ClassTypeOfSpecialization();
typeOfSpecialization.TypeOfSpecializationId = dr.GetInt32("Type_of_specialization_id");
typeOfSpecialization.TypeOfSpecialization = dr.GetString("Type_of_specialization");
dct.TypeOfSpecialization = typeOfSpecialization;
ClassSpecialization specialization = new ClassSpecialization();
specialization.SpecializationId = dr.GetInt32("Specialization_id");
specialization.Specialization = dr.GetString("Specialization");
dct.Specialization = specialization;
dct.Status = new ClassStatus();
dct.Status.StatusId = dr.GetInt32("Status_id");
dct.Status.Status = dr.GetString("Status");
DoctorList.Add(dct);
}
ClassQuerry.CloseConnection();
return(DoctorList);
}
public static void CreateTerms(string text)
{
if (text.Length == 0)
{
return;
}
string querry = "Use [db_Clinic] INSERT INTO [dbo].[tbl_Term] (Start_time,End_time,Date,Calendar_doctor_id,Calendar_day_id,Office_id,Doctor_id)" +
"Values " + text;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
ClassQuerry.CloseConnection();
}
public static int GetDayIdForCalendarDate(int calendarId, int dayOfMonth)
{
string querry = "SELECT dbo.tbl_Calendar_Day.Calendar_day_id FROM [dbo].[tbl_Calendar_Day], [dbo].[tbl_Calendar] WHERE [dbo].[tbl_Calendar_Day].Calendar_id = [dbo].[tbl_Calendar].Calendar_id AND [dbo].[tbl_Calendar_Day].Calendar_id = " + calendarId + " AND [dbo].[tbl_Calendar_Day].Day = " + dayOfMonth;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
int dayId = 0;
while (dr.Read())
{
dayId = dr.GetInt32("Calendar_day_id");
}
ClassQuerry.CloseConnection();
return(dayId);
}
public static int SelectCalendarDayId(int Day, int Calendar_id)
{
string querry = "Use [db_Clinic] SELECT Calendar_day_id FROM [tbl_Calendar_Day] WHERE DAY = " + Day + " AND Calendar_id = " + Calendar_id;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
int id = 0;
while (dr.Read())
{
id = dr.GetInt32("Calendar_day_id");
}
ClassQuerry.CloseConnection();
return(id);
}
public static int SelectCalendarId(int year, int month)
{
string querry = "USE [db_Clinic] SELECT [Calendar_id] FROM [dbo].[tbl_Calendar] WHERE [Year] = " + year + " AND [Month]=" + month;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
int id = 0;
while (dr.Read())
{
id = dr.GetInt32("Calendar_id");
}
ClassQuerry.CloseConnection();
return(id);
}
public static int GetCalendarIdForDoctor(int doctorId, int calendarId)
{
string querry = "SELECT[dbo].[tbl_Calendar_doctor].Calendar_doctor_id FROM[dbo].[tbl_Calendar_doctor] WHERE dbo.tbl_Calendar_doctor.Doctor_id = " + doctorId +
" AND dbo.tbl_Calendar_doctor.Calendar_id = " + calendarId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
int calendar_doctor_Id = 0;
while (dr.Read())
{
calendar_doctor_Id = dr.GetInt32("Calendar_doctor_id");
}
ClassQuerry.CloseConnection();
return(calendar_doctor_Id);
}
public static int SelectStatusId(EnumStatus enumstatus)
{
string status = ClassStatus.StatusString(enumstatus);
string querry = "USe db_Clinic SELECT Status_id FROM tbl_Status WHERE Status='" + status + "'";
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
int id = 0;
while (dr.Read())
{
id = dr.GetInt32("Status_id");
}
ClassQuerry.CloseConnection();
return(id);
}
public static List <ClassStatus> StatusList()
{
string querry = "Use[db_Clinic] SELECT[Status_id],[Status] FROM[dbo].[tbl_Status]";
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassStatus> StatusList = new List <ClassStatus>();
while (dr.Read())
{
ClassStatus Status = new ClassStatus();
Status.StatusId = dr.GetInt32("Status_id");
Status.Status = dr.GetString("Status");
StatusList.Add(Status);
}
ClassQuerry.CloseConnection();
return(StatusList);
}
public static List <ClassFixedTerms> ListFixedTermsForSpecifiedDoctor(int doctorId)
{
string querry = "USE [db_Clinic] SELECT [Day],[Start],[End] FROM tbl_Fixed_terms WHERE Doctor_id=" + doctorId;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassFixedTerms> FixedTermsList = new List <ClassFixedTerms>();
while (dr.Read())
{
ClassFixedTerms term = new ClassFixedTerms();
term.Day = dr.GetInt32("Day");
term.Start = dr.GetTimeSpan(1);
term.End = dr.GetTimeSpan(2);
FixedTermsList.Add(term);
}
ClassQuerry.CloseConnection();
return(FixedTermsList);
}
public static List <ClassAppointment> GetAllAppoitments()
{
string querry = "SELECT [dbo].[tbl_Appointment].[Appointment_id], [dbo].[tbl_Term].Date, [dbo].[tbl_Patient].Patient_id, [dbo].[tbl_Patient].Name AS Patient_name, [dbo].[tbl_Patient].Surname AS Patient_surname, " +
"[dbo].[tbl_Patient].Personal_identity_number as PESEL, [dbo].[tbl_Patient].Phone_number AS Patient_phone_number," +
"[dbo].[tbl_Doctor].Doctor_id, [dbo].[tbl_Employee].Name AS Doctor_name, [dbo].[tbl_Employee].Surname AS Doctor_surname, " +
"[dbo].[tbl_Appointment].add_description as Description, [dbo].[tbl_Appointment].Topic " +
"FROM[dbo].[tbl_Appointment], [dbo].[tbl_Term], [dbo].[tbl_Doctor], [dbo].[tbl_Employee], [dbo].[tbl_Patient]" +
"WHERE[dbo].[tbl_Term].Term_id = [dbo].[tbl_Appointment].Term_id AND[dbo].[tbl_Term].Doctor_id = [dbo].[tbl_Doctor].Doctor_id AND[dbo].[tbl_Doctor].Employee_id = [dbo].[tbl_Employee].Employee_id AND[dbo].[tbl_Appointment].Patient_id = [dbo].[tbl_Patient].Patient_id";
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassAppointment> appointment = new List <ClassAppointment>();
while (dr.Read())
{
ClassAppointment appointmentDataGrid = new ClassAppointment();
appointmentDataGrid.AppointmendtId = dr.GetInt32("Appointment_id");
appointmentDataGrid.Term = new ClassTerm();
appointmentDataGrid.Term.Date = dr.GetDateTime(1);
appointmentDataGrid.Patient = new DictionariesHanding.ClassPatient();
appointmentDataGrid.Patient.PatientId = dr.GetInt32("Patient_id");
appointmentDataGrid.Patient.Name = dr.GetString("Patient_name");
appointmentDataGrid.Patient.Surname = dr.GetString("Patient_surname");
appointmentDataGrid.Patient.PersonalIdentityNumber = dr.GetString("PESEL");
appointmentDataGrid.Patient.PhoneNumber = dr.GetString("Patient_phone_number");
appointmentDataGrid.Doctor = new DictionariesHanding.ClassDoctor();
appointmentDataGrid.Doctor.Doctor_id = dr.GetInt32("Doctor_id");
appointmentDataGrid.Doctor.Employee = new DictionariesHanding.ClassEmployee();
appointmentDataGrid.Doctor.Employee.Name = dr.GetString("Doctor_name");
appointmentDataGrid.Doctor.Employee.Surname = dr.GetString("Doctor_surname");
appointmentDataGrid.Topic = dr.GetString("Topic");
try
{
appointmentDataGrid.Description = dr.GetString("Description");
}
catch (System.Data.SqlTypes.SqlNullValueException ex)
{
appointmentDataGrid.Description = "Brak";
}
appointment.Add(appointmentDataGrid);
}
ClassQuerry.CloseConnection();
return(appointment);
}
