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