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; }