public void GetReservationWhenTime(string timeReservation)
{
int j = 0;
int temp = 0;
int startOpenOfficeHour = 8;
int endOpenOfficeHour = 16;
int startOpenOfficeMinutes = 30;
string tempNamePatient = "";
choosenIDTherapist = SelectedTherapistsListToReservation.Substring(0, 2);
DateTime currentTime = DateTime.Now;
for (j = startOpenOfficeHour; j <= endOpenOfficeHour; j++) // Looping throw colums
{
temp = 100 + j;
data[j] = temp.ToString();
if (j == 14)
{
setCounterColorsReservation.setCounterColors();
}
}
DBClass.openConnection();
DBClass.sql = "select distinct IDReservation, DataVisitStart, DataVisitEnd, RoomVisit, AcceptedVisit, IDPatientReservation, firstNameUser, lastNameUser, FirstNameTherapist, LastNameTherapist, reservations.IDTherapist, CONVERT(varchar(5), TimeStartVisit) " +
"from reservations, patients, therapists, users " +
"where therapists.LastNameTherapist = (select distinct LastNameTherapist from therapists where IDTherapist = '" + choosenIDTherapist + "') " +
"and reservations.IDTherapist = '" + choosenIDTherapist + "' " +
"and users.firstNameUser = (select firstNameUser from users where IDUsers = reservations.IDUser) " +
"and users.lastNameUser = (select lastNameUser from users where IDUsers = reservations.IDUser) " +
"and convert(Date, DataVisitStart) = '" + dateForReservationList + "' and TimeStartVisit = '" + timeReservation + "' " +
"ORDER BY DataVisitStart Asc";
// wybierze tylko z daty zadanej
//select* from reservations where convert(Date, DataVisitStart) = '2020-01-22'and convert(time, DataVisitStart) = '10:00:00' ORDER BY DataVisitStart Asc
//"where therapists.LastNameTherapist = (select LastNameTherapist from therapists where IDTherapist = '" + choosenIDTherapist + "') " +
//"where reservation.IDTherapist = '" + choosenIDTherapist + "' " +
DBClass.cmd.CommandType = CommandType.Text;
DBClass.cmd.CommandText = DBClass.sql;
DBClass.da = new SqlDataAdapter(DBClass.cmd);
DBClass.dt = new DataTable();
DBClass.da.Fill(DBClass.dt);
using (SqlDataReader reader = DBClass.cmd.ExecuteReader())
{
while (reader.Read())
{
for (j = 0; j <= reader.FieldCount - 1; j++) // Looping throw colums
{
dataRes[j] = reader.GetValue(j).ToString();
}
Console.WriteLine("User05 - " + dataRes[5]);
// ustawka na puste pola
if (dataRes[5].Length > 0)
{
tempNamePatient = patientsListToReservation[dataRes[5]];
}
ReservationsList.Add(new ReservationModel
{
IDReservation = dataRes[0],
DataVisitStart = dataRes[1].Substring(0, 10),
DataVisitEnd = dataRes[2],
RoomVisit = dataRes[3],
AcceptedVisit = dataRes[4],
IDPatient = dataRes[5],
FirstNameUser = dataRes[6],
LastNameUser = dataRes[7],
FirstNameTherapist = dataRes[8],
LastNameTherapist = dataRes[9],
TimeStartVisit = dataRes[10],
textToReservationView = dataRes[6] + " " + dataRes[7] + " - [ " + tempNamePatient + " ]"
});
DBClass.closeConnection();
return;
}
ReservationsList.Add(new ReservationModel
{
IDReservation = "",
DataVisitStart = dateForReservationList,
DataVisitEnd = "",
RoomVisit = "",
AcceptedVisit = "",
IDPatient = "",
FirstNameUser = "",
LastNameUser = "",
FirstNameTherapist = "",
LastNameTherapist = "",
TimeStartVisit = timeReservation,
textToReservationView = "Wolny termin"
});;
DBClass.closeConnection();
//return;
}
}
public void DataGrid_Loaded()
{
int j = 0;
int temp = 0;
DateTime currentTime = DateTime.Now;
int startOpenOfficeHour = 8;
int endOpenOfficeHour = 16;
int startOpenOfficeMinutes = 30;
for (j = startOpenOfficeHour; j <= endOpenOfficeHour; j++) // Looping throw colums
{
temp = 100 + j;
data[j] = temp.ToString();
if (j == 14)
{
setCounterColorsReservation.setCounterColors();
}
}
DBClass.openConnection();
DBClass.sql = "select distinct IDReservation, DataVisitStart, DataVisitEnd, RoomVisit, AcceptedVisit, FirstName, LastName, LastNameTherapist, TimeStartVisit " +
"from reservations, patients, therapists " +
"where therapists.LastNameTherapist = (select LastNameTherapist from therapists where IDTherapist = '1') " +
"and patients.FirstName = (select FirstName from patients where IDPatient = reservations.IDPatient) " +
"and patients.LastName = (select LastName from patients where IDPatient = reservations.IDPatient) " +
"and convert(Date, DataVisitStart) = '2020-01-22' and TimeStartVisit = '08:00:00' " +
"ORDER BY DataVisitStart Asc";
// pełna wersja
//"select distinct IDReservation, DataVisitStart, DataVisitEnd, RoomVisit, AcceptedVisit, FirstName, LastName, LastNameTherapist " +
// "from reservations, patients, therapists " +
// "where(reservations.IDPatient = '1') and therapists.LastNameTherapist = (select LastNameTherapist from therapists where IDTherapist = '1') " +
// "and patients.FirstName = (select FirstName from patients where IDPatient = '1') " +
// "and patients.LastName = (select LastName from patients where IDPatient = '1')";
// wybierze tylko z daty zadanej
//select* from reservations where convert(Date, DataVisitStart) = '2020-01-22'and convert(time, DataVisitStart) = '10:00:00' ORDER BY DataVisitStart Asc
DBClass.cmd.CommandType = CommandType.Text;
DBClass.cmd.CommandText = DBClass.sql;
DBClass.da = new SqlDataAdapter(DBClass.cmd);
DBClass.dt = new DataTable();
DBClass.da.Fill(DBClass.dt);
using (SqlDataReader reader = DBClass.cmd.ExecuteReader())
{
while (reader.Read())
{
for (j = 0; j <= reader.FieldCount - 1; j++) // Looping throw colums
{
dataRes[j] = reader.GetValue(j).ToString();
Console.WriteLine("REzerW - " + dataRes[j]);
}
ReservationsList.Add(new ReservationModel
{
IDReservation = dataRes[0],
DataVisitStart = dataRes[1].Substring(0, 8),
DataVisitEnd = dataRes[2],
RoomVisit = dataRes[3],
AcceptedVisit = dataRes[4],
FirstName = dataRes[5],
LastName = dataRes[6],
LastNameTherapist = dataRes[7],
TimeStartVisit = dataRes[8]
//Console.WriteLine(" Lista id : " + dataRes[0] + dataRes[6]);
});;
}
}
DBClass.closeConnection();
//foreach (var item in ReservationsList.Skip(1).Take(3))
//{
// Console.WriteLine(" Lista rezerwacji : " + item.LastName);
//}
}
