/// <summary>
/// get all the trainers from the database
/// </summary>
/// <returns>list of trainers</returns>
public List<Trainer> getTrainers()
{
List<Trainer> trainers = new List<Trainer>();
string query = "Select * FROM activitytrainers ;";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
MySqlDataReader personReader = cmd.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
String trainerCertifcate = (String)personReader["trainerCertifcate"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
Trainer tr = new Trainer(first_name, last_name, address, phone_number, email, personId, permmsion, sallery, trainerCertifcate);
tr.setDBid((int)personReader["idTrainer"]);
trainers.Add(tr);
}
personReader.Close();
return trainers;
}
/// <summary>
/// get all the employess from the data base
/// </summary>
/// <returns>return a list of employees</returns>
public List<Employee> getAllEmployee()
{
// Add all managers to the list
List<Employee> EmployeeList = new List<Employee>();
string query = "Select * FROM administration;";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
String first_name = (String)dataReader["F_Name"];
String last_name = (String)dataReader["L_Name"];
String address = (String)dataReader["address"];
String phone_number = (String)dataReader["phone_number"];
String email = (String)dataReader["email"];
int personId = (int)dataReader["person_id"];
int permmsion = (int)dataReader["Premission"];
float sallery = (float)dataReader["Sal_Hour"];
Manager person = new Manager(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)dataReader["idAdministration"]);
EmployeeList.Add(person);
}
dataReader.Close();
// Add all instructor to the list
string query2 = "Select * FROM instructor;";
MySqlCommand cmd2 = new MySqlCommand(query2, this.connection);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
while (dataReader2.Read())
{
String first_name = (String)dataReader2["F_Name"];
String last_name = (String)dataReader2["L_Name"];
String address = (String)dataReader2["address"];
String phone_number = (String)dataReader2["phone_number"];
String email = (String)dataReader2["email"];
int personId = (int)dataReader2["person_id"];
int permmsion = (int)dataReader2["Premission"];
float sallery = (float)dataReader2["Sal_Hour"];
//Console.Write(first_name);
Instructor person = new Instructor(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)dataReader2["idInstructor"]);
EmployeeList.Add(person);
}
dataReader2.Close();
// Add all trainers to the list
string query3 = "Select * FROM trainers;";
MySqlCommand cmd3 = new MySqlCommand(query3, this.connection);
MySqlDataReader dataReader3 = cmd3.ExecuteReader();
while (dataReader3.Read())
{
String first_name = (String)dataReader3["F_Name"];
String last_name = (String)dataReader3["L_Name"];
String address = (String)dataReader3["address"];
String phone_number = (String)dataReader3["phone_number"];
String email = (String)dataReader3["email"];
int personId = (int)dataReader3["person_id"];
int permmsion = (int)dataReader3["Premission"];
float sallery = (float)dataReader3["Sal_Hour"];
String certificate = (String)dataReader3["certificate"];
//Console.Write(first_name);
Trainer person = new Trainer(first_name, last_name, address, phone_number, email, personId, permmsion, sallery,certificate);
person.setDBid((int)dataReader3["idtrainer"]);
EmployeeList.Add(person);
}
dataReader3.Close();
// Add all reception to the list
string query4 = "Select * FROM reception;";
MySqlCommand cmd4 = new MySqlCommand(query4, this.connection);
MySqlDataReader dataReader4 = cmd4.ExecuteReader();
while (dataReader4.Read())
{
String first_name = (String)dataReader4["F_Name"];
String last_name = (String)dataReader4["L_Name"];
String address = (String)dataReader4["address"];
String phone_number = (String)dataReader4["phone_number"];
String email = (String)dataReader4["email"];
int personId = (int)dataReader4["person_id"];
int permmsion = (int)dataReader4["Premission"];
float sallery = (float)dataReader4["Sal_Hour"];
Receptionist person = new Receptionist(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)dataReader4["idReception"]);
EmployeeList.Add(person);
}
dataReader4.Close();
return EmployeeList;
}
/// <summary>
/// serach for employee in the data base , based on the employee id number
/// </summary>
/// <param name="id"></param>
/// <returns> an instance of the employee </returns>
public Employee getEmployeeById(long id)
{
Employee person = null;
String type = null;
string query = "Select * FROM users WHERE person_id='" + id + "';";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
type = (String)dataReader["type"];
Console.Write(type);
}
dataReader.Close();
if (type != null)
{
switch (type)
{
case "manager":
{
string queryPerson = "Select * FROM administration WHERE person_id='" + id + "' ;";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Manager(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)personReader["idAdministration"]);
}
personReader.Close();
break;
}
case "instructor":
{
string queryPerson = "Select * FROM instructor WHERE person_id='" + id + "' ;";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Instructor(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)personReader["idInstructor"]);
}
personReader.Close();
break;
}
case "reception":
{
string queryPerson = "Select * FROM reception WHERE person_id='" + id + "' ;";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Receptionist(first_name, last_name, address, phone_number, email, personId, permmsion, sallery);
person.setDBid((int)personReader["idReception"]);
}
personReader.Close();
break;
}
case "trainer":
{
string queryPerson = "Select * FROM trainers WHERE person_id='" + id + "' ;";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
String certificate = (String)personReader["certificate"];
person = new Trainer(first_name, last_name, address, phone_number, email, personId, permmsion, sallery, certificate);
person.setDBid((int)personReader["idtrainer"]);
}
personReader.Close();
break;
}
}
}
return person;
}
/// <summary>
/// get an id and a password from the login form, search for it in the user table and if it exisets returns the right person object
/// </summary>
/// <param name="id"></param>
/// <param name="password"></param>
/// <returns>null if the user was not found , else return the person object from the database</returns>
public Person getPersonByCredentials(long id, String password)
{
Person person = null;
String type = null;
string query = "Select * FROM users WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
type = (String)dataReader["type"]; //get the user type from the users table
}
dataReader.Close();
if (type != null)
{
//Create query for the right person type (customer, manager, reception, instructor, trainer)
switch (type)
{
case "manager":
{
string queryPerson = "Select * FROM administration WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Manager(first_name, last_name, address, phone_number, email, personId, permmsion, sallery); //create a new manager
person.setDBid((int)personReader["idAdministration"]); //set the uniqe id from the mysql table
}
personReader.Close();
break;
}
case "customer":
{
string queryPerson = "Select * FROM cilent WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["Address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["eMail"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
String DataJoind = (String)personReader["DateJoind"];
String Md_LastDate = (String)personReader["Md_LastDate"];
int class_miss = (int)personReader["class_miss"];
//Console.Write(first_name);
person = new Customer(first_name, last_name, address, phone_number, email, personId, permmsion, DataJoind, Md_LastDate, class_miss); //create a new customer instance
person.setDBid((int)personReader["idCilent"]); //set the uniqe id from the mysql table
}
personReader.Close();
((Customer)person).setListGymClass(getCustomerGymClassByDbId(person.getDBid()));
((Customer)person).setListTrainingProgram(getCustomerTPByDbId(person.getDBid()));
break;
}
case "instructor":
{
string queryPerson = "Select * FROM instructor WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Instructor(first_name, last_name, address, phone_number, email, personId, permmsion, sallery); //crate a new instructor instance
person.setDBid((int)personReader["idInstructor"]); //set the uniqe id from the mysql table
}
personReader.Close();
break;
}
case "reception":
{
string queryPerson = "Select * FROM reception WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
person = new Receptionist(first_name, last_name, address, phone_number, email, personId, permmsion, sallery); //create a new reception instance
person.setDBid((int)personReader["idReception"]); //set the uniqe id from the mysql table
}
personReader.Close();
break;
}
case "trainer":
{
string queryPerson = "Select * FROM trainers WHERE person_id='" + id + "' AND password=md5('" + password + "');";
MySqlCommand cmdPerson = new MySqlCommand(queryPerson, this.connection);
MySqlDataReader personReader = cmdPerson.ExecuteReader();
while (personReader.Read())
{
String first_name = (String)personReader["F_Name"];
String last_name = (String)personReader["L_Name"];
String address = (String)personReader["address"];
String phone_number = (String)personReader["phone_number"];
String email = (String)personReader["email"];
int personId = (int)personReader["person_id"];
int permmsion = (int)personReader["Premission"];
float sallery = (float)personReader["Sal_Hour"];
String certificate = (String)personReader["certificate"];
person = new Trainer(first_name, last_name, address, phone_number, email, personId, permmsion, sallery, certificate); //create a new trainer instance
person.setDBid((int)personReader["idtrainer"]); //set the uniqe id from the mysql table
}
personReader.Close();
break;
}
}
}
return person;
}