//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);
}
private void bLLogin_Click(object sender, EventArgs e)
{
//Variables to be used: 1x bool, 4x string
bool loggedIn = false;
string lUsername = "", lPassword = "";
//check if boxes are empty, the Trim removes white space in text from either side
if ("".Equals(tbLUserName.Text.Trim()) || "".Equals(tbLPassword.Text.Trim()))
{
MessageBox.Show("Please make sure you enter a Username and Password");
return;
}
//Get the username and password and store as string
lUsername = tbLUserName.Text.Trim();
lPassword = tbLPassword.Text.Trim();
//Use a select query on the users table
SQL.selectQuery("SELECT * from Librarian");
//If there exists at least one User
if (SQL.read.HasRows)
{
//cycle through all users checking if the username exists and if the password matches
while (SQL.read.Read())
{
//SQL.read[i]: i=0 is UserName ... i=4 is Password
if (lUsername.Equals(SQL.read[4].ToString()) && lPassword.Equals(SQL.read[5].ToString()))
{
//Username and Password correct, get fname, lname to display
loggedIn = true;
lID = SQL.read[0].ToString();
break; //stops the while loop since they have logged in
}
}
}
else
{
//Error message to show that no users have been registered
MessageBox.Show("No users have been registered 1.");
return;
}
//if logged in display a success message
if (loggedIn)
{
//message stating we logged in good
MessageBox.Show("hurray");
}
else
{
//message stating we couldn't log in
MessageBox.Show("Login attempt unsuccessful! Please check details");
tbMUserName.Focus();
return;
}
}
public void setVariables()
{
SQL.selectQuery("SELECT * from Child");
//If there exists at least one User
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//cycle through all users checking if the username exists and if the password matches
if (mID.Equals(SQL.read[0].ToString()))
{
fname = SQL.read[1].ToString();
lname = SQL.read[2].ToString();
iwi = SQL.read[3].ToString();
DOB = SQL.read[4].ToString();
gender = SQL.read[5].ToString();
note = SQL.read[6].ToString();
childLanguage = SQL.read[7].ToString();
return;
}
}
}
else
{
//Error message to show that no users have been registered
MessageBox.Show("No users have been registered 3.");
return;
}
}
// It compares the username and password with the data of a table and returns a message
private string checkLogin(string username, string password, string table)
{
string message = "Login attempt unsuccessful! Please check details";
string loginQuery = $"SELECT * FROM {table}";
//gets data from database
SQL.selectQuery(loginQuery);
//Check that there is something to write brah
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
if (username.Equals(SQL.read[0].ToString()) && password.Equals(SQL.read[1].ToString()))
{
message = $"Successfully logged in as: {SQL.read[2].ToString()} {SQL.read[3].ToString()}";
//user sessionUser = new user();
//sessionUser.userFirstName = SQL.read[2].ToString();
//sessionUser.userLastName = SQL.read[3].ToString();
break;
}
}
}
else
{
message = "No users have been registered yet";
}
return(message);
}
private void ToyCategoryItems()
{
string title;
SQL.selectQuery("select * from Category");
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
title = SQL.read[1].ToString();
cbToyCategory.Items.Add(title);
}
}
}
//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 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);
}
//********************************************* 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 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);
}
private void displayToys()
{
SQL.selectQuery("Select * from Toy");
//string query = "select * from Toy where";
//if (1.Text().equals("") )
//{ query.}
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//assigning values to the database values
name = SQL.read[1].ToString();
image = SQL.read[11].ToString();
description = SQL.read[2].ToString();
type = SQL.read[10].ToString();
location = SQL.read[6].ToString();
repCost = SQL.read[4].ToString();
cost = SQL.read[5].ToString();
status = SQL.read[9].ToString();
//adding data to the Listview
string[] row = { image, name, description };
ListViewItem lvi = new ListViewItem(image);
lvi.SubItems.Add(name);
lvi.SubItems.Add(description);
lvi.SubItems.Add(type);
lvi.SubItems.Add(cost);
lvi.SubItems.Add(repCost);
lvi.SubItems.Add(location);
lvToys.Items.Add(lvi);
//lvToys.Items.Add(image, name, description);
}
}
}
//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 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 usernames of the Instructors
public static List <string> getInstructorsUsernames()
{
List <String> instructorsUserNamesList = new List <string>();
//It creates the rows as per slot times
string instructorsQuery = "SELECT username FROM Instructors";
//It gets data from database
SQL.selectQuery(instructorsQuery);
//It checks that there is something to write
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
//It saves the data into a list
instructorsUserNamesList.Add(SQL.read[0].ToString());
}
}
return(instructorsUserNamesList);
}
//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 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;
}
/// <summary>
/// Gets the social media id based on the social media name
/// </summary>
/// <param name="socialMedia">The name of the social media from combo box</param>
/// <returns>The ID of the social media from database, blank string returned if not in database</returns>
private string getMediaID(string socialMedia)
{
//string for return variable
string SocMediaID = "";
//query getting the ID based on the social media name
SQL.selectQuery("SELECT SocMediaID FROM SocialMedia WHERE SocialMediaName LIKE \'" + socialMedia + "\'");
//if there exists an ID in database, and its not blank then make socMedia ID = something
if (SQL.read.HasRows)
{
//read from the table to store in its array
SQL.read.Read();
//if the data read is not blank then
if (!("".Equals(SQL.read[0].ToString())))
{
//make the ID variable equal the database value
SocMediaID = SQL.read[0].ToString();
}
}
//else it will leave socMediaID as a blank string and return that
return(SocMediaID);
}
// It checks if they username doesn't exist in the DB already
private bool checkUsername(string username, string myTable)
{
bool usernameOK = true;
//It loads the Client Type Combobox
//string usernameQuery = "SELECT username FROM Clients";
string usernameQuery = "SELECT username FROM " + myTable;
//It gets data from database
SQL.selectQuery(usernameQuery);
//It checks that there is something to write brah
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
if (username.Equals(SQL.read[0].ToString()))
{
usernameOK = false;
break;
}
}
}
return(usernameOK);
}
/// <summary>
/// Prints out the ID based on the query givin into a combo box
/// </summary>
/// <param name="comboBox">A control to be used to write existing names to</param>
/// <param name="query">An SQL query to generate from</param>
public static void editComboBoxItems(ComboBox comboBox, string query)
{
bool clear = true;
//gets data from database
SQL.selectQuery(query);
//Check that there is something to write brah
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
if (comboBox.Text == SQL.read[0].ToString())
{
clear = false;
}
}
}
//gets data from database
SQL.selectQuery(query);
//if nothing in the comboBox then we need to clear it
if (clear)
{
comboBox.Text = "";
comboBox.Items.Clear();
}
// this will print whatever is in the database to the combobox
if (SQL.read.HasRows)
{
while (SQL.read.Read())
{
comboBox.Items.Add(SQL.read[0].ToString());
}
}
}
/// <summary>
/// Clicked when user decides they are ready to log in,
/// Will get username and password, use that to query database and check that username and password are correct.
/// A message box will be used to state whether or not we logged in successfully
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonLogin_Click(object sender, EventArgs e)
{
//Variables to be used: 1x bool, 4x string
bool loggedIn = false;
string username = "", password = "";
//check if boxes are empty, the Trim removes white space in text from either side
if ("".Equals(tbMUserName.Text.Trim()) || "".Equals(tbMPassword.Text.Trim()))
{
MessageBox.Show("Please make sure you enter a Username and Password");
return;
}
//Get the username and password and store as string
username = tbMUserName.Text.Trim();
password = tbMPassword.Text.Trim();
//Use a select query on the users table
SQL.selectQuery("SELECT * from Membership");
//If there exists at least one User
if (SQL.read.HasRows)
{
//cycle through all users checking if the username exists and if the password matches
while (SQL.read.Read())
{
//SQL.read[i]: i=0 is UserName ... i=4 is Password
if (username.Equals(SQL.read[1].ToString()) && password.Equals(SQL.read[0].ToString()))
{
//Username and Password correct, get fname, lname to display
mID = SQL.read[3].ToString();
//MessageBox.Show(mID);
loggedIn = true;
break; //stops the while loop since they have logged in
}
}
}
else
{
//Error message to show that no users have been registered
MessageBox.Show("No users have been registered 1.");
return;
}
//if logged in display a success message
if (loggedIn)
{
//MessageBox.Show("Cool");
Members membersLogin = new Members(mID);
this.Hide();
membersLogin.ShowDialog();
this.Close();
//message stating we logged in good
//listBox1.Visible = true;
//label1.Visible = false;
//LoginAs.Visible = true;
//setVariables();
//string a = "First name:", b = "Last name:", c = "iwi?", d = "Birthday:", l = "Gender:", f = "Note:", g = "Language:";
//listBox1.Items.Add(a);
//listBox1.Items.Add(fname);
//listBox1.Items.Add(b);
//listBox1.Items.Add(lname);
//listBox1.Items.Add(c);
//listBox1.Items.Add(iwi);
//listBox1.Items.Add(d);
//listBox1.Items.Add(DOB);
//listBox1.Items.Add(l);
//listBox1.Items.Add(gender);
//listBox1.Items.Add(f);
//listBox1.Items.Add(note);
//listBox1.Items.Add(g);
//listBox1.Items.Add(childLanguage);
}
else
{
//message stating we couldn't log in
MessageBox.Show("Login attempt unsuccessful! Please check details");
tbMUserName.Focus();
return;
}
}