//********************************************* Methods interacting with the Database *******************************************
//It inserts the new Schedules for the selected Date and all the Instructors
private void insertNewSchedulesForInstructor(string instructor, string[][] schedules)
{
//It checks the data from the matrix coming from the ListView
for (int i = 1; i < schedules.Length - 1; i++) //days
{
string myDate = listViewWeek.Columns[i].Text;
for (int j = 0; j < schedules[0].Length - 1; j++) //hours
{
//The information to proceed with the INSERT query
string myTime = timesOfDate[j];
string value = schedules[i][j];
int slotId = 1;
//IT checks that value=1
if (value != "0")
{
if (!DBData.checkExistingAppointment(instructor, myDate, myTime))
{
var insertQuery = $"INSERT INTO Appointments (usernameInstructor, idTimeSlot, slotDate, slotTime) VALUES('{instructor}', {slotId}, '{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}', '{myTime}')";
SQL.executeQuery(insertQuery);
}
}
}
}
MessageBox.Show("Instructors Schedules saved successfully.");
}
//It inserts the Appointment Data in the DB
private void bookAppointment()
{
string updateQuery = $"UPDATE Appointments SET usernameClient='{userName}' WHERE usernameInstructor='{instructor}' AND slotDate='{ControlFunctions.formatToSQLDate(selectedDate)}' AND slotTime='{appointmentTime}'";
SQL.executeQuery(updateQuery);
}
//It gets the Appointments of the Client from today
private List <List <string> > getClientAppointments(string userName, DateTime myDate)
{
//It creates a List for the Appointments
List <List <string> > clientAppointmentsList = new List <List <string> >();
//It creates three lists for each of the interesting fields to extract from the DB
List <String> clientAppointmentDatesList = new List <string>();
List <String> clientAppointmentTimesList = new List <string>();
List <String> clientAppointmentInstructorsList = new List <string>();
//It obtains the data from the DB
string clientAppointmentsQuery = $"SELECT slotDate, slotTime, usernameInstructor FROM Appointments WHERE usernameClient='{userName}' AND slotDate >= '{ControlFunctions.formatToSQLDate(myDate)}' ORDER BY slotDate, slotTime";
SQL.selectQuery(clientAppointmentsQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//It saves the data into a List
clientAppointmentDatesList.Add(SQL.read[0].ToString());
clientAppointmentTimesList.Add(SQL.read[1].ToString());
clientAppointmentInstructorsList.Add(SQL.read[2].ToString());
}
}
//It adds the sublists to the Appointments List
clientAppointmentsList.Add(clientAppointmentDatesList);
clientAppointmentsList.Add(clientAppointmentTimesList);
clientAppointmentsList.Add(clientAppointmentInstructorsList);
return(clientAppointmentsList);
}
//It inserts the new Schedules for the selected Date and all the Instructors confirming the Schedules
private void insertNewSchedules(string myDate, string[][] schedules)
{
//It checks the data from the matrix coming from the ListView
for (int i = 1; i < schedules.Length - 1; i++)
{
for (int j = 0; j < schedules[0].Length - 1; j++)
{
//The information to proceed with the INSERT query
string instructor = instructorsUserNames[i - 1];
string carAssignedForWeek = getCarsAssignedToInstructor(instructor, firstDayOfWeek, lastDayOfWeek);
//string carLicense = cars[i - 1];
string myTime = timesOfDate[j];
string value = schedules[i][j];
int slotId = 1;
//IT checks that value=1
if (value != "0")
{
if (!DBData.checkExistingAppointment(instructor, myDate, myTime))
{
var insertQuery = "";
if (carAssignedForWeek == "")
{
insertQuery = $"INSERT INTO Appointments (usernameInstructor, idTimeSlot, slotDate, slotTime, confirmed) VALUES('{instructor}', {slotId}, '{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}', '{myTime}', 1)";
}
else
{
insertQuery = $"INSERT INTO Appointments (usernameInstructor, idTimeSlot, slotDate, slotTime, carLicense, confirmed) VALUES('{instructor}', {slotId}, '{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}', '{myTime}', '{carAssignedForWeek}', 1)";
}
SQL.executeQuery(insertQuery);
}
}
}
}
MessageBox.Show("Instructors Schedules saved successfully. Now assign a car to the required Instructors.");
}
//********************************************* Methods interacting with the Database *******************************************
//It deletes an Appointment
private void freeClientAppointment(string myDate, string myTime, string myInstructor)
{
string deleteQuery = $"UPDATE Appointments SET usernameClient=NULL WHERE usernameInstructor='{myInstructor}' AND slotDate='{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}' AND slotTime='{myTime}'";
SQL.executeQuery(deleteQuery);
}
//********************************************* Methods interacting with the Database *******************************************
//It gets the car assigned for a Instructor in a week
private string getCarsAssignedToInstructor(string instructor, DateTime myDate1, DateTime myDate2)
{
String carsAssigned = "";
string carAssignedQuery = $"SELECT DISTINCT carLicense FROM Appointments WHERE usernameInstructor = '{instructor}' AND slotDate >= '{ControlFunctions.formatToSQLDate(myDate1)}' AND slotDate <= '{ControlFunctions.formatToSQLDate(myDate2)}' AND carLicense IS NOT null";
SQL.selectQuery(carAssignedQuery);
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//It saves the data into a list
carsAssigned = SQL.read[0].ToString();
}
}
return(carsAssigned);
}
//It gets the available cars for the week
private List <string> getAvailableCars(DateTime myDate1, DateTime myDate2)
{
List <String> availableCarsList = new List <string>();
//It creates the rows as per slot times
string availableCarsQuery = $"SELECT license FROM Cars WHERE license NOT IN (SELECT DISTINCT carLicense FROM Appointments WHERE slotDate >= '{ControlFunctions.formatToSQLDate(myDate1)}' AND slotDate <= '{ControlFunctions.formatToSQLDate(myDate2)}' AND carLicense IS NOT null)";
//It gets data from database
SQL.selectQuery(availableCarsQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//It saves the data into a list
availableCarsList.Add(SQL.read[0].ToString());
}
}
return(availableCarsList);
}
//It deletes the existing Schedules for a Instructor and between the Dates that don't have an Appointment with a Client nor have been confirmed by Admin
public static void deleteExistingSchedulesForInstructorAndDates(string instructor, string myDate1, string myDate2)
{
var deleteQuery = $"DELETE FROM Appointments WHERE usernameInstructor='{instructor}' AND slotDate>='{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate1))}' AND slotDate<='{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate2))}' AND usernameClient IS null AND confirmed is null";
SQL.executeQuery(deleteQuery);
}
//It deletes the existing Schedules for all the Instructors and the Date that don't have an Appointment with a Client
public static void deleteExistingSchedulesForInstructorsAndDate(string myDate)
{
var deleteQuery = $"DELETE FROM Appointments WHERE slotDate='{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}' AND usernameClient IS null";
SQL.executeQuery(deleteQuery);
}
//It checks that the Instructor has not assigned more than 40 hours per Week *******************Not used
public static int getHoursAssignedPerWeek(DateTime myDate1, DateTime myDate2, string instructor)
{
//bool hoursLower40 = true;
int hours = 0;
string slotNumberQuery = $"SELECT COUNT(id) FROM Appointments WHERE usernameInstructor = '{instructor}' AND slotDate >= '{ControlFunctions.formatToSQLDate(myDate1)}' AND slotDate <= '{ControlFunctions.formatToSQLDate(myDate2)}'";
SQL.selectQuery(slotNumberQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
hours = int.Parse(SQL.read[0].ToString());
//if (int.Parse(SQL.read[0].ToString()) > 40)
//{
// hoursLower40 = false;
//}
}
}
return(hours);
//return hoursLower40;
}
//It updates the Car assignations
public static void updateCarAssignation(string car, string instructor, DateTime myDate1, DateTime myDate2)
{
try
{
string updateQuery = $"UPDATE Appointments SET carLicense='{car}' WHERE usernameInstructor='{instructor}' AND slotDate>= '{ControlFunctions.formatToSQLDate(myDate1)}' AND slotDate<= '{ControlFunctions.formatToSQLDate(myDate2)}'";
SQL.executeQuery(updateQuery);
MessageBox.Show("Car Assignations saved successfully.");
}
catch (Exception ex)
{
MessageBox.Show($"There was a problem when updating the Car assignations: {ex}.");
}
}
//It checks if there is already an Appointment in the DB for the Instructor, Date and Time
public static bool checkExistingAppointment(string instructor, string myDate, string myTime)
{
bool existAppointment = false;
string slotTimeQuery = $"SELECT slotTime FROM Appointments WHERE usernameInstructor= '{instructor}' AND slotDate = '{ControlFunctions.formatToSQLDate(DateTime.Parse(myDate))}' AND slotTime='{myTime}'";
//It gets data from database
SQL.selectQuery(slotTimeQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
existAppointment = true;
}
return(existAppointment);
}
//It gets the usernames of the Instructors
public static List <string> getTimesOfDay(DateTime myDate1)
{
List <String> timesList = new List <string>();
//It creates the rows as per slot times
string slotTimesQuery = "SELECT DISTINCT slotTime FROM TimeSlots WHERE slotDate= '" + ControlFunctions.formatToSQLDate(myDate1) + "'";
//It gets data from database
SQL.selectQuery(slotTimesQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//It saves the data into a list
timesList.Add(SQL.read[0].ToString());
}
}
return(timesList);
}
//It gets the number of hours scheduled in a day for an instructor
public static string getInstructorHoursScheduledForDate(string instructor, DateTime myDate)
{
string hoursScheduled;
//It creates the rows as per slot times
string hoursQuery = $"SELECT COUNT(id) FROM Appointments WHERE usernameInstructor='{instructor}' AND slotDate='{ControlFunctions.formatToSQLDate(myDate)}'";
//It gets data from database
SQL.selectQuery(hoursQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
SQL.read.Read();
hoursScheduled = SQL.read[0].ToString();
}
else
{
hoursScheduled = "";
}
return(hoursScheduled);
}
//It gets the times scheduled for an instructor in a date
public static List <string> getInstructorScheduleForDate(string instructor, DateTime myDate)
{
List <String> instructorSchedule = new List <string>();
string slotTimeQuery = "SELECT slotTime FROM Appointments WHERE usernameInstructor= '" + instructor + "' AND slotDate = '" + ControlFunctions.formatToSQLDate(myDate) + "'";
//It gets data from database
SQL.selectQuery(slotTimeQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
instructorSchedule.Add(SQL.read[0].ToString());
}
}
return(instructorSchedule);
}
//It checks if that Date and Time exist Slots available
public static bool checkExistingsStlots(DateTime myDate, string myTime)
{
bool existSlots = false;
string slotTimeQuery = $"SELECT slotTime FROM TimeSlots WHERE slotDate = '{ControlFunctions.formatToSQLDate(myDate)}' AND slotTime = '{myTime}'";
//It gets data from database
SQL.selectQuery(slotTimeQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
existSlots = true;
}
return(existSlots);
}
