//It deletes the Appointment
private void buttonDelete_Click(object sender, EventArgs e)
{
if (listViewAppointments.SelectedItems.Count > 0)
{
DialogResult clientDecision = MessageBox.Show("Are you sure you want to delete that Appointment?", "Appointment Deletion", MessageBoxButtons.YesNo);
switch (clientDecision)
{
case DialogResult.Yes:
var myDate = listViewAppointments.SelectedItems[0].Text;
var myTime = listViewAppointments.SelectedItems[0].SubItems[1].Text;
var myInstructor = listViewAppointments.SelectedItems[0].SubItems[2].Text;
//It updates the DB freing the client
freeClientAppointment(myDate, myTime, myInstructor);
//It loads the Appointments for the Client
clientAppointments = getClientAppointments(userName, DateTime.Today);
//It refreshes the Appointments ListView
listViewAppointments.Items.Clear();
createListViewAppointmentsReport();
//It refreshes the Appointment Selection ListView
ControlFunctions.clearListViewReport(listViewAppointmentSelection);
createListViewAppointmentSelectionReport();
MessageBox.Show("Appointment deleted successfully.");
break;
case DialogResult.No:
break;
}
}
else
{
MessageBox.Show("Please select an Appointment first.");
}
}
//********************************************* Event Methods *****************************************************************
//It creates the ListView for the instructor to create his Schedule
private void dateTimePickerWeek_ValueChanged(object sender, EventArgs e)
{
listViewWeek.Visible = true;
//clearVariables();
//It loads the Times of the selected Date
timesOfDate = DBData.getTimesOfDay(dateTimePickerWeek.Value);
//It creates the Day ListView
ControlFunctions.clearListViewReport(listViewWeek);
createlistViewWeekReport(dateTimePickerWeek.Value);
}
public AdminConsole()
{
InitializeComponent();
instructorsUserNames = DBData.getInstructorsUsernames();
ControlFunctions.populateCombo(comboBoxInstructor, instructorsUserNames);
listViewDay.Visible = false;
listViewWeek.Visible = false;
listViewCarsAssigned.Visible = false;
}
private void listViewDay_MouseDown(object sender, MouseEventArgs e)
{
var info = listViewDay.HitTest(e.X, e.Y);
if (info.Item != null)
{
var row = info.Item.Index;
var col = info.Item.SubItems.IndexOf(info.SubItem);
if (info.SubItem != null)
{
var value = info.Item.SubItems[col].Text;
if (col > 0 && col < instructorsUserNames.Count + 1 && row < timesOfDate.Count)
{
if (value == "0")
{
if (DBData.checkExistingsStlots(DateTime.Parse(labelDate.Text), listViewDay.Items[row].Text))
{
int weekHours = DBData.getHoursAssignedPerWeek(firstDayOfWeek, lastDayOfWeek, listViewDay.Columns[col].Text);
int DayHours = DBData.getHoursAssignedPerDay(DateTime.Parse(labelDate.Text), listViewDay.Columns[col].Text);
int newDayHours = int.Parse(listViewDay.Items[listViewDay.Items.Count - 1].SubItems[col].Text) + 1;
int newWeekHours = weekHours - DayHours + newDayHours;
if (int.Parse(listViewDay.Items[listViewDay.Items.Count - 1].SubItems[col].Text) < 8 && newWeekHours <= 40)
{
ControlFunctions.changeListViewSubItemValue(listViewDay, row, col, "1");
ControlFunctions.recalculateTotal(listViewDay, row, col, 1);
}
else
{
MessageBox.Show("You cannot assign more than 8hours/day and 40hours/week.");
}
}
else
{
MessageBox.Show("There are no slots available for that Date and Time. Please select another one.");
}
}
else
{
if (DBData.checkAppointmentOccupiedByClient(labelDate.Text, listViewDay.Items[row].Text, instructorsUserNames[col - 1]))
{
ControlFunctions.changeListViewSubItemValue(listViewDay, row, col, "0");
ControlFunctions.recalculateTotal(listViewDay, row, col, 0);
}
else
{
MessageBox.Show("That Slot cannot be changed because there is already an Appointment with a Client.");
}
}
}
}
}
}
//It obtains the slot clicked and activates or desactivates it
private void listViewWeek_MouseDown(object sender, MouseEventArgs e)
{
var info = listViewWeek.HitTest(e.X, e.Y);
if (info.Item != null)
{
var row = info.Item.Index;
var col = info.Item.SubItems.IndexOf(info.SubItem);
if (info.SubItem != null)
{
var value = info.Item.SubItems[col].Text;
if (col > 0 && col < daysOfWeek + 1 && row < timesOfDate.Count - 1)
{
if (value == "0")
{
//It checks that there are Slots available for the Instructor to assign his availability
if (DBData.checkExistingsStlots(DateTime.Parse(listViewWeek.Columns[col].Text), listViewWeek.Items[row].Text))
{
//It checks that the Instructor has not selected more than 8 hours per Day and 40 per Week
if (int.Parse(listViewWeek.Items[listViewWeek.Items.Count - 1].SubItems[col].Text) < 8 && int.Parse(listViewWeek.Items[listViewWeek.Items.Count - 1].SubItems[listViewWeek.Columns.Count - 1].Text) < 40)
{
ControlFunctions.changeListViewSubItemValue(listViewWeek, row, col, "1");
ControlFunctions.recalculateTotal(listViewWeek, row, col, 1);
}
else
{
MessageBox.Show("You cannot assign more than 8hours/day and 40hours/week.");
}
}
else
{
MessageBox.Show("There are no slots available for that Date and Time. Please select another one.");
}
}
else
{
//It checks if the Appointment is already booked by a Client
if (DBData.checkAppointmentConfirmedByAdmin(listViewWeek.Columns[col].Text, listViewWeek.Items[row].Text, instructorUserName))
{
ControlFunctions.changeListViewSubItemValue(listViewWeek, row, col, "0");
ControlFunctions.recalculateTotal(listViewWeek, row, col, 0);
}
else
{
MessageBox.Show("That Slot cannot be changed because it was confirmed by the Administrator.");
}
}
}
}
}
}
private void listViewWeek_SelectedIndexChanged(object sender, EventArgs e)
{
if (listViewWeek.SelectedIndices.Count != 0)
{
var myIndex = listViewWeek.SelectedIndices[0];
var dateSelected = listViewWeek.Items[myIndex].Text;
if (myIndex < daysOfWeek)
{
labelDate.Text = dateSelected;
ControlFunctions.clearListViewReport(listViewDay);
createListViewDayReport(DateTime.Parse(dateSelected));
}
}
}
//It creates the ListView Report for the Week
private void createListViewWeekReport()
{
//It creates the columns of the ListView
ControlFunctions.createListViewColumns(listViewWeek, "Date", instructorsUserNames, "", 80, 68, 0);
//It calculates the first and last date of the week
int dayOfWeekSelected = (int)dateTimePickerWeek.Value.DayOfWeek;
firstDayOfWeek = dateTimePickerWeek.Value.Date.AddDays(-dayOfWeekSelected + 1);
lastDayOfWeek = firstDayOfWeek.Date.AddDays(daysOfWeek - 1);
//It creates a list of the dates of the week
List <String> datesList = new List <string>();
for (int j = 0; j < daysOfWeek; j++)
{
datesList.Add(firstDayOfWeek.AddDays(j).ToString("d"));
}
//It adds the total per Instructor
datesList.Add("Total");
//It creates the items of the ListView
ControlFunctions.createListViewItems(listViewWeek, datesList);
//It creates the array of arrays of hours scheduled per day and instructor and one more for the totals
string[][] hoursScheduledMatrix = new string[daysOfWeek + 1][];
for (int j = 0; j < daysOfWeek + 1; j++)
{
hoursScheduledMatrix[j] = new string[instructorsUserNames.Count];
}
//It fills in the arrays
for (int i = 0; i < daysOfWeek; i++)
{
for (int j = 0; j < instructorsUserNames.Count; j++)
{
//It obtains the list of hours scheduled for the Instructors and Dates
hoursScheduledMatrix[i][j] = DBData.getInstructorHoursScheduledForDate(instructorsUserNames[j], firstDayOfWeek.AddDays(i));
}
}
//It adds the total per Instructor and week summing all the days
for (int j = 0; j < instructorsUserNames.Count; j++)
{
hoursScheduledMatrix[daysOfWeek][j] = "0";
for (int i = 0; i < daysOfWeek; i++)
{
hoursScheduledMatrix[daysOfWeek][j] = (int.Parse(hoursScheduledMatrix[daysOfWeek][j]) + int.Parse(hoursScheduledMatrix[i][j])).ToString();
}
}
//It copies all the data to the listView creating the Subitems
ControlFunctions.createListViewSubitems(listViewWeek, hoursScheduledMatrix);
}
private void buttonSaveSchedule_Click(object sender, EventArgs e)
{
if (listViewWeek.Items.Count > 0)
{
string[][] LVData = ControlFunctions.obtainDataFromListView(listViewDay);
DBData.deleteExistingSchedulesForInstructorsAndDate(labelDate.Text);
insertNewSchedules(labelDate.Text, LVData);
ControlFunctions.clearListViewReport(listViewWeek);
createListViewWeekReport();
}
else
{
MessageBox.Show("Please select first a date and plan a Schedule.");
}
}
//********************************************* Event Methods *****************************************************************
private void dateTimePickerDate_ValueChanged(object sender, EventArgs e)
{
//It loads the Times of the selected Date
selectedDate = dateTimePickerDate.Value;
timesOfDate = DBData.getTimesOfDay(selectedDate);
//It makes the ListViews visibles
listViewAppointmentSelection.Visible = true;
listViewAppointments.Visible = true;
//It creates the Appointment Selection ListView
ControlFunctions.clearListViewReport(listViewAppointmentSelection);
createListViewAppointmentSelectionReport();
//It loads the Appointments for the Client
clientAppointments = getClientAppointments(userName, DateTime.Today);
}
//It obtains the desired Appointment from the ListView
private void listViewAppointmentSelection_MouseDown(object sender, MouseEventArgs e)
{
var info = listViewAppointmentSelection.HitTest(e.X, e.Y);
if (info.Item != null)
{
var row = info.Item.Index;
var col = info.Item.SubItems.IndexOf(info.SubItem);
if (info.SubItem != null)
{
var value = info.Item.SubItems[col].Text;
if (col > 0)
{
switch (value)
{
case slotAvailable:
appointmentTime = listViewAppointmentSelection.Items[row].Text;
instructor = listViewAppointmentSelection.Columns[col].Text;
if (checkAppointmentDuplicity())
{
MessageBox.Show("There is already an Appointment that day at that time with another Instructor. Please delete the Appointment first.");
}
else
{
if (checkReservationDuplicity())
{
MessageBox.Show("You cannot book two Appointments at once. Click in other reserved Appointment to free it first.");
}
else
{
ControlFunctions.changeListViewSubItemValue(listViewAppointmentSelection, row, col, slotReserved);
labelAppointmentSelected.Text = $"{selectedDate.ToString("d")} at {appointmentTime} with {instructor}";
}
}
break;
case slotReserved:
appointmentTime = "";
instructor = "";
ControlFunctions.changeListViewSubItemValue(listViewAppointmentSelection, row, col, slotAvailable);
labelAppointmentSelected.Text = "No appointment selected";
break;
}
}
}
}
}
//It creates the ListView Report for the Week
private void createListViewAppointmentSelectionReport()
{
//It gets the usernames of the Instructors and creates the columns of the ListView
ControlFunctions.createListViewColumns(listViewAppointmentSelection, "Time", instructorsUserNames, "", 60, 65, 0);
//It creates the ListView Items
ControlFunctions.createListViewItems(listViewAppointmentSelection, timesOfDate);
//It creates the array of arrays of hours scheduled per Time and Instructor
string[][] hoursScheduledMatrix = new string[timesOfDate.Count][];
for (int j = 0; j < timesOfDate.Count; j++)
{
hoursScheduledMatrix[j] = new string[instructorsUserNames.Count];
}
//It initializes the arrays to zeros
for (int j = 0; j < instructorsUserNames.Count; j++)
{
for (int i = 0; i < timesOfDate.Count; i++)
{
hoursScheduledMatrix[i][j] = "";
}
}
//It fills in the arrays
for (int j = 0; j < instructorsUserNames.Count; j++)
{
List <string> timeScheduled = new List <string>();
//It gets the Schedule for each Instructor for the selected Date excluding already existing Appointments
timeScheduled = DBData.getInstructorFreeScheduleForDate(instructorsUserNames[j], selectedDate);
for (int i = 0; i < timesOfDate.Count; i++)
{
foreach (string time in timeScheduled)
{
if (timesOfDate[i] == time)
{
hoursScheduledMatrix[i][j] = "available";
break;
}
else
{
hoursScheduledMatrix[i][j] = "";
}
}
}
}
//It copies all the data to the listView in the Subitems
ControlFunctions.createListViewSubitems(listViewAppointmentSelection, hoursScheduledMatrix);
}
//It saves the Schedule for the Week************************************take care with the confirmed ones from the Admin, dont delete them and dont write them again
private void buttonSaveSchedule_Click(object sender, EventArgs e)
{
if (listViewWeek.Items.Count > 0)
{
//It obtains the new schedule from the Day ListView
string[][] LVData = ControlFunctions.obtainDataFromListView(listViewWeek);
//It deletes the schedules for that Date and Instructor in the DB
DBData.deleteExistingSchedulesForInstructorAndDates(instructorUserName, firstDayOfWeek.ToString("d"), lastDayOfWeek.ToString("d"));
//It inserts the new Schedules for that Date and instructor in the DB
insertNewSchedulesForInstructor(instructorUserName, LVData);
//It actualizes the Week ListView
ControlFunctions.clearListViewReport(listViewWeek);
createlistViewWeekReport(dateTimePickerWeek.Value);
}
else
{
MessageBox.Show("Please select first a date and plan a Schedule.");
}
}
//********************************************* Methods ************************************************************************
//It checks if here is any other reserved in the ListView
private bool checkReservationDuplicity()
{
//It obtains the new schedule from the Day ListView
string[][] LVData = ControlFunctions.obtainDataFromListView(listViewAppointmentSelection);
bool duplicity = false;
//It checks the data from the matrix coming from the ListView
for (int i = 1; i < LVData.Length; i++)
{
for (int j = 0; j < LVData[0].Length; j++)
{
if (LVData[i][j] == slotReserved)
{
duplicity = true;
break;
}
}
}
return(duplicity);
}
private void buttonSaveCar_Click(object sender, EventArgs e)
{
if (comboBoxInstructor.SelectedIndex != -1 && comboBoxCar.SelectedIndex != -1)
{
if (checkInstructorScheduled(comboBoxInstructor.Text))
{
DBData.updateCarAssignation(comboBoxCar.Text, comboBoxInstructor.Text, firstDayOfWeek, lastDayOfWeek);
ControlFunctions.clearListViewReport(listViewCarsAssigned);
createListViewAssignedCarsReport();
populateCarCombo();
}
else
{
MessageBox.Show($"You have to select a week with scheduled slots for {comboBoxInstructor.Text}.");
}
}
else
{
MessageBox.Show("Please select an Instructor and assing a Car with the Comboboxes below.");
}
}
private void dateTimePickerWeek_ValueChanged(object sender, EventArgs e)
{
listViewDay.Visible = true;
listViewWeek.Visible = true;
listViewCarsAssigned.Visible = true;
comboBoxInstructor.SelectedItem = -1;
comboBoxInstructor.Text = "";
comboBoxCar.SelectedItem = -1;
comboBoxCar.Text = "";
populateCarCombo();
timesOfDate = DBData.getTimesOfDay(dateTimePickerWeek.Value);
ControlFunctions.clearListViewReport(listViewWeek);
createListViewWeekReport();
ControlFunctions.clearListViewReport(listViewCarsAssigned);
createListViewAssignedCarsReport();
labelDate.Text = dateTimePickerWeek.Value.ToString("d");
ControlFunctions.clearListViewReport(listViewDay);
createListViewDayReport(dateTimePickerWeek.Value);
}
private void createListViewAssignedCarsReport()
{
//It creates the columns of the ListView
ControlFunctions.createListViewColumns(listViewCarsAssigned, "Instructor", instructorsUserNames, "", 80, 68, 0);
//It creates the items of the ListView
List <string> carsTitle = new List <string>();
carsTitle.Add("Assigned");
ControlFunctions.createListViewItems(listViewCarsAssigned, carsTitle);
//It creates the Subitems of the ListView
//It creates the array of arrays of hours scheduled per day and instructor and one more for the totals
string[][] carsAssigned = new string[1][];
carsAssigned[0] = new string[instructorsUserNames.Count];
//List<string> carsAssigned = new List<string>();
for (int i = 0; i < instructorsUserNames.Count; i++)
{
carsAssigned[0][i] = getCarsAssignedToInstructor(instructorsUserNames[i], firstDayOfWeek, lastDayOfWeek);
}
//It copies all the data to the listView creating the Subitems
ControlFunctions.createListViewSubitems(listViewCarsAssigned, carsAssigned);
}
//It saves the Appointment
private void buttonConfirm_Click(object sender, EventArgs e)
{
//It updates the DB
if (instructor != "" && appointmentTime != "")
{
bookAppointment();
//It loads the Appointments for the Client
clientAppointments = getClientAppointments(userName, DateTime.Today);
//It refreshes the Appointments ListView
listViewAppointments.Items.Clear();
createListViewAppointmentsReport();
//It refreshes the Appointment Selection ListView
ControlFunctions.clearListViewReport(listViewAppointmentSelection);
createListViewAppointmentSelectionReport();
labelAppointmentSelected.Text = "No appointment selected";
appointmentTime = "";
MessageBox.Show("Appointment booked successfully.");
}
else
{
MessageBox.Show("Please select a slot first.");
}
}
//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 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}.");
}
}
//********************************************* 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.");
}
//********************************************* 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 cancels the Schedule for the Week and retrieves the data from the DB
private void buttonCancelSchedule_Click(object sender, EventArgs e)
{
//It actualizes the Week ListView
ControlFunctions.clearListViewReport(listViewWeek);
createlistViewWeekReport(dateTimePickerWeek.Value);
}
//It creates the ListView Report for the Week
private void createlistViewWeekReport(DateTime myDate)
{
//It creates a list of the dates of the week
List <String> weekDatesList = new List <string>(createDaysOfWeekList());
//It gets the usernames of the Instructors and creates the columns of the ListView
ControlFunctions.createListViewColumns(listViewWeek, "Time", weekDatesList, "Total", 60, 75, 55);
//It gets the times of the day and creates the Items of the ListView
timesOfDate = DBData.getTimesOfDay(dateTimePickerWeek.Value);
//It adds the total per Instructor
timesOfDate.Add("Total");
//It creates the ListView Items
ControlFunctions.createListViewItems(listViewWeek, timesOfDate);
//It creates the array of arrays of hours scheduled per Time and Instructor plus one for the Totals
string[][] hoursScheduledMatrix = new string[timesOfDate.Count][];
for (int j = 0; j < timesOfDate.Count; j++)
{
hoursScheduledMatrix[j] = new string[weekDatesList.Count + 1];
}
//It initializes the arrays to zeros
for (int j = 0; j < weekDatesList.Count; j++)
{
for (int i = 0; i < timesOfDate.Count; i++)
{
hoursScheduledMatrix[i][j] = "0";
}
}
//It fills in the arrays
for (int j = 0; j < weekDatesList.Count - 1; j++)
{
List <string> timesScheduled = new List <string>();
//It gets the Schedule for each Instructor for the selected Date
timesScheduled = DBData.getInstructorScheduleForDate(instructorUserName, DateTime.Parse(weekDatesList[j]));
for (int i = 0; i < timesOfDate.Count; i++)
{
foreach (string time in timesScheduled)
{
if (timesOfDate[i] == time)
{
hoursScheduledMatrix[i][j] = "1";
break;
}
else
{
hoursScheduledMatrix[i][j] = "0";
}
}
}
}
//It adds the Totals per Time
for (int i = 0; i < timesOfDate.Count; i++)
{
hoursScheduledMatrix[i][weekDatesList.Count] = "0";
for (int j = 0; j < weekDatesList.Count; j++)
{
hoursScheduledMatrix[i][weekDatesList.Count] = (int.Parse(hoursScheduledMatrix[i][weekDatesList.Count]) + int.Parse(hoursScheduledMatrix[i][j])).ToString();
}
}
//It adds the Totals per Day
for (int j = 0; j < weekDatesList.Count; j++)
{
hoursScheduledMatrix[timesOfDate.Count - 1][j] = "0";
for (int i = 0; i < timesOfDate.Count - 1; i++)
{
hoursScheduledMatrix[timesOfDate.Count - 1][j] = (int.Parse(hoursScheduledMatrix[timesOfDate.Count - 1][j]) + int.Parse(hoursScheduledMatrix[i][j])).ToString();
}
}
//It adds the Total per Week
hoursScheduledMatrix[timesOfDate.Count - 1][weekDatesList.Count] = "0";
for (int i = 0; i < timesOfDate.Count - 1; i++)
{
hoursScheduledMatrix[timesOfDate.Count - 1][weekDatesList.Count] = (int.Parse(hoursScheduledMatrix[timesOfDate.Count - 1][weekDatesList.Count]) + int.Parse(hoursScheduledMatrix[i][weekDatesList.Count])).ToString();
}
//It copies all the data to the listView in the Subitems
ControlFunctions.createListViewSubitems(listViewWeek, hoursScheduledMatrix);
}
//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.");
}
//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 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 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);
}
//********************************************* 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);
}
private void buttonCancelSchedule_Click(object sender, EventArgs e)
{
ControlFunctions.clearListViewReport(listViewDay);
createListViewDayReport(DateTime.Parse(labelDate.Text));
}
//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);
}
