//********************************************* 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); }