/// <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>
/// add training progrem to a customer
/// </summary>
/// <param name="edit"></param>
/// <param name="tp"></param>
public void AddCustomerTrainingProgrem(Customer edit, TrainingProgram tp) {
Dictionary<String, int> excersisses = this.getExcerssiesBackwords();
String tpList = "";
foreach(Exercise ex in tp.getExerciseList()) {
tpList = tpList + excersisses[ex.getExerciseName()] + "_" + ex.getExerciseRepeats() + "_" + ex.getExerciseSets() + ",";
}
tpList = tpList.Substring(0, tpList.Length - 1);
string query = "INSERT INTO training_program (exersie_list) VALUES('" + tpList + "');";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
cmd.ExecuteNonQuery();
long tpLastId = cmd.LastInsertedId;
string query2 = "INSERT INTO client_program (program_id, client_id) VALUES ('"+ tpLastId +"', '" + edit.getDBid() + "');";
MySqlCommand cmd2 = new MySqlCommand(query2, this.connection);
cmd2.ExecuteNonQuery();
}
/// <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;
}
public void deleteCostumerGymClass(GymClass gymclass, Customer customer)
{
string query = "DELETE FROM client_activity WHERE client_id = '" + customer.getDBid() + "' AND activity_id = '" + gymclass.getDBid() + "';";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
cmd.ExecuteNonQuery();
string query2 = "UPDATE activity SET listed=listed - 1 WHERE id='" + gymclass.getDBid() + "';";
MySqlCommand cmd2 = new MySqlCommand(query2, this.connection);
cmd2.ExecuteNonQuery();
}
public void updateCostumerGymClass(GymClass gymclass, Customer customer) {
string query = "INSERT INTO client_activity (client_id, activity_id) VALUES('"+customer.getDBid()+"', '"+gymclass.getDBid()+"');";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
cmd.ExecuteNonQuery();
string query2 = "UPDATE activity SET listed=listed + 1 WHERE id='" + gymclass.getDBid() + "';";
MySqlCommand cmd2 = new MySqlCommand(query2, this.connection);
cmd2.ExecuteNonQuery();
}
public void deleteCustomer(Customer customer) {
string query = "DELETE FROM cilent WHERE idCilent='" + customer.getDBid() + "';";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
cmd.ExecuteNonQuery();
string query2 = "DELETE FROM users WHERE person_id = '" + customer.getIdNumber() + "' AND type = 'customer';";
MySqlCommand cmd2 = new MySqlCommand(query2, this.connection);
cmd2.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show(customer.getNameFirst()+" "+customer.getNameLast()+" Was Deleted");
}
public String EditCustomer(Customer add) {
string query = "UPDATE cilent SET F_Name='" + add.getNameFirst() + "', L_Name='" + add.getNameLast() + "', Md_LastDate = '"+add.getMedicalClearanceExpiration() +"' ,phone_number='" + add.getPhoneNumber() + "', person_id='" + add.getIdNumber() + "', address='" + add.getHomeAddress() + "', email='" + add.getEmailAddress() + "' WHERE idCilent='" + add.getDBid() + "';";
MySqlCommand cmd = new MySqlCommand(query, this.connection);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
if (ex.Message.Contains("Duplicate"))
{
if (ex.Message.Contains("ClientIDNum"))
{
return "ID";
}
else if (ex.Message.Contains("PhoneNumber"))
{
return "Phone Number";
}
else if (ex.Message.Contains("eMail"))
{
return "Email";
}
}
else
System.Windows.Forms.MessageBox.Show("Unknow error");
}
return "O.K";
}