public List<Customer> getListedCustomerByClassID(int classId) {
List<Customer> customerList = new List<Customer>();
string query = "Select * FROM cilent INNER JOIN client_activity ON cilent.idCilent = client_activity.client_id WHERE client_activity.activity_id ='" + classId + "';";
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"];
String DataJoind = (String)dataReader["DateJoind"];
String Md_LastDate = (String)dataReader["Md_LastDate"];
int class_miss = (int)dataReader["class_miss"];
//Console.Write(first_name);
Customer temp = new Customer(first_name, last_name, address, phone_number, email, personId, permmsion, DataJoind, Md_LastDate, class_miss);
temp.setDBid((int)dataReader["idCilent"]);
customerList.Add(temp);
}
dataReader.Close();
return customerList;
}
/// <summary>
/// look for a customer in the data base by its id number
/// </summary>
/// <param name="id"></param>
/// <returns>returns a customer instance by a given id</returns>
public Customer getCusomerById(int id)
{
Customer temp = null;
string queryPerson = "Select * FROM cilent 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"];
String DataJoind = (String)personReader["DateJoind"];
String Md_LastDate = (String)personReader["Md_LastDate"];
int class_miss = (int)personReader["class_miss"];
temp = new Customer(first_name, last_name, address, phone_number, email, personId, permmsion, DataJoind, Md_LastDate, class_miss);
temp.setDBid((int)personReader["idCilent"]);
}
personReader.Close();
if (temp != null) {
((Customer)temp).setListGymClass(getCustomerGymClassByDbId(temp.getDBid())); //sets the customr gymclasses
((Customer)temp).setListTrainingProgram(getCustomerTPByDbId(temp.getDBid())); // sets the customer training progrem
}
return temp;
}
/// <summary>
/// get all the customer from the data base
/// </summary>
/// <returns>return a list of customers</returns>
public List<Customer> getAllCustomer() {
List<Customer> customerList = new List<Customer>();
string query = "Select * FROM cilent;";
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"];
String DataJoind = (String)dataReader["DateJoind"];
String Md_LastDate = (String)dataReader["Md_LastDate"];
int class_miss = (int)dataReader["class_miss"];
Customer temp = new Customer(first_name, last_name, address, phone_number, email, personId, permmsion, DataJoind, Md_LastDate, class_miss);
temp.setDBid((int)dataReader["idCilent"]);
customerList.Add(temp);
}
dataReader.Close();
return customerList;
}
/// <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;
}
