/*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 List <ClassTerm> AppointmentsDateForCombobox()
{
string querry = "SELECT * FROM [dbo].[tbl_Term] WHERE [dbo].[tbl_Term].Date >= GETDATE() AND Doctor_id =" + ClassLoggedDoctor.Doctor_Id;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassTerm> terms = new List <ClassTerm>();
while (dr.Read())
{
ClassTerm termsCombobox = new ClassTerm();
termsCombobox.TermId = dr.GetInt32("Term_id");
termsCombobox.StartTime = dr.GetTimeSpan(1);
termsCombobox.EndTime = dr.GetTimeSpan(2);
termsCombobox.Date = dr.GetDateTime("Date");
terms.Add(termsCombobox);
}
ClassQuerry.CloseConnection();
return(terms);
}
public static List <ClassTerm> GetListOfWorkingDayInCurrentMonth(int doctor_id)
{
string querry = " SELECT Term_id, Start_time, End_time, Date FROM [dbo].[tbl_Term] WHERE Doctor_id = " + doctor_id +
" AND YEAR(Date) = YEAR(GETDATE()) AND MONTH(Date) = MONTH(GETDATE())";
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassTerm> TermList = new List <ClassTerm>();
while (dr.Read())
{
ClassTerm term = new ClassTerm();
term.TermId = dr.GetInt32("Term_id");
term.StartTime = dr.GetTimeSpan(1);
term.EndTime = dr.GetTimeSpan(2);
term.Date = dr.GetDateTime("Date");
term.IsWorkingDay = true;
TermList.Add(term);
}
ClassQuerry.CloseConnection();
return(TermList);
}
public static List <ClassTerm> TermsForSpecifiedYearAndMonth(int year, int month)
{
string querry = "Use [db_Clinic] SELECT [Term_id],[Start_time],[End_time],[Date],[Office_id],[Doctor_id] " +
"FROM [dbo].[tbl_Term] WHERE YEAR(Date)= " + year + " AND MONTH(Date) = " + month;
SqlDataReader dr = ClassQuerry.ExecuteQuerry(querry);
List <ClassTerm> TermList = new List <ClassTerm>();
while (dr.Read())
{
ClassTerm Term = new ClassTerm();
Term.TermId = dr.GetInt32("Term_id");
Term.StartTime = dr.GetTimeSpan(1);
Term.EndTime = dr.GetTimeSpan(2);
Term.Date = dr.GetDateTime("Date");
Term.Office = new DictionariesHanding.ClassOffice();
Term.Office.OfficeId = dr.GetInt32("Office_id");
Term.Doctor = new DictionariesHanding.ClassDoctor();
Term.Doctor.Doctor_id = dr.GetInt32("Doctor_id");
TermList.Add(Term);
}
ClassQuerry.CloseConnection();
return(TermList);
}
public List <ClassTerm> ListOfTerms()
{
string querry = "Use [db_Clinic] SELECT [Term_id],[Start_time],[End_time],[Date],[Office_id],[Doctor_id] " +
"FROM [dbo].[tbl_Term]";
ClassQuerry q = new ClassQuerry();
SqlDataReader dr = q.ExecuteQuerry(querry);
List <ClassTerm> TermList = new List <ClassTerm>();
while (dr.Read())
{
ClassTerm Term = new ClassTerm();
Term.TermId = dr.GetInt32("Term_id");
Term.StartTime = dr.GetTimeSpan(1);
Term.EndTime = dr.GetTimeSpan(2);
Term.Date = dr.GetDateTime("Date");
Term.Office = new DictionariesHanding.ClassOffice();
Term.Office.OfficeId = dr.GetInt32("Office_id");
Term.Doctor = new DictionariesHanding.ClassDoctor();
Term.Doctor.Doctor_id = dr.GetInt32("Doctor_id");
TermList.Add(Term);
}
q.CloseConnection();
return(TermList);
}