//********************************************* 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 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 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}.");
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
var updateQuery = $"DELETE FROM Clients WHERE username = '******' ";
SQL.executeQuery(updateQuery);
MessageBox.Show("Account Deleted.");
return;
}
catch (Exception)
{
MessageBox.Show("Could not delete account. Please Try Again.");
return;
}
}
//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);
}
// It registers the new user as long as all controls hold text
private void buttonRegister_Click(object sender, EventArgs e)
{
//variables to be used
string username = "", password = "", password2 = "", firstname = "", lastname = "", email = "", clientType = "", userType = "";
Int32 phone = 0;
//It checks that the text and combo boxes have something typed in them
bool hasText = checkTextControls();
if (!hasText)
{
MessageBox.Show("Please make sure all fields have data.");
comboBoxUserType.Focus();
return;
}
//It checks that the username doesn't exist yet in the DB
bool userOK = checkUsername(textBoxUserName.Text.Trim(), comboBoxUserType.Text);
if (!userOK)
{
MessageBox.Show("This user already exists in the Data Base, please try another one.");
textBoxUserName.BackColor = Color.LightCoral;
textBoxUserName.Focus();
return;
}
//It checks that the password==password2
bool passOK = checkPassword(textBoxPassword.Text.Trim(), textBoxPassword2.Text.Trim());
if (!passOK)
{
MessageBox.Show("Please make sure the password in both password fields is the same.");
textBoxPassword.BackColor = Color.LightCoral;
textBoxPassword2.BackColor = Color.LightCoral;
textBoxPassword.Focus();
return;
}
//It checks that the email is a proper one
bool emailOK = checkEmail(textBoxEmail.Text.Trim());
if (!emailOK)
{
MessageBox.Show("Please insert a proper email in the field.");
textBoxEmail.BackColor = Color.LightCoral;
textBoxEmail.Focus();
return;
}
//It checks that the phone number introduced is numbers if the control is enabled
if (textBoxPhone.Enabled == true)
{
bool phoneOK = int.TryParse(textBoxPhone.Text.Trim(), out phone);
if (!phoneOK)
{
MessageBox.Show("You can only write numbers in the phone field.");
textBoxPhone.BackColor = Color.LightCoral;
textBoxPhone.Focus();
return;
}
}
//(1) GET the data from the textboxes and store into variables created above, good to put in a try catch with error message
try
{
userType = comboBoxUserType.Text;
username = textBoxUserName.Text.Trim();
password = textBoxPassword.Text.Trim();
password2 = textBoxPassword2.Text.Trim();
firstname = textBoxFirst.Text.Trim();
lastname = textBoxLast.Text.Trim();
email = textBoxEmail.Text.Trim();
switch (userType)
{
case "Admins":
break;
case "Clients":
phone = int.Parse(textBoxPhone.Text.Trim());
clientType = comboBoxClientType.Text.Trim();
break;
case "Instructors":
phone = int.Parse(textBoxPhone.Text.Trim());
break;
default:
break;
}
}
catch
{
//Error message, more useful when you are storing numbers etc. into the database.
MessageBox.Show("Please make sure your data is in correct format.");
return;
}
//(2) Execute the INSERT statement, making sure all quotes and commas are in the correct places.
try
{
var insertClientQuery = "";
//It sends a different insert statement depending on the User Type Selection
switch (userType)
{
case "Admins":
insertClientQuery = $"INSERT INTO {userType} VALUES('{username}', '{password}', '{firstname}', '{lastname}', '{email}')";
break;
case "Clients":
insertClientQuery = $"INSERT INTO {userType} VALUES('{username}', '{password}', '{firstname}', '{lastname}', '{email}', {phone}, '{clientType}')";
break;
case "Instructors":
insertClientQuery = $"INSERT INTO {userType} VALUES('{username}', '{password}', '{firstname}', '{lastname}', '{email}', {phone})";
break;
default:
break;
}
SQL.executeQuery(insertClientQuery);
}
catch (Exception)
{
MessageBox.Show("Register attempt unsuccessful. Check insert statement. Could be a Username conflict too.");
return;
}
//success message for the user to know it worked
MessageBox.Show("Successfully Registered as " + userType + ": " + firstname + " " + lastname + ". Your username is: " + username);
//Go back to the login page since we registered successfully to let the user log in
Hide(); //hides the register form
LoginPage login = new LoginPage(); //creates the login page as an object
login.ShowDialog(); //shows the new login page form
this.Close(); //closes the register form that was hidden
}
//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 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);
}
