/** * @desc Executes when the "Accept" button is clicked * It checks the use rname and password and loads in the profile * @params [none] No input parameter. * @return [none] No directly returned data. */ private void button_accept_Click(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create password md5 hash format string md5Hash = Utils.CreateMD5Hash(txt_password.Text.Trim()); // Launch the query List<Hashtable> lhResultset = conn.lhSqlQuery("Select * from users where login='******' and password = '******' and active = 1"); // If the user with the given credentials was not found if ((int)lhResultset.Count != 1) MessageBox.Show("The username or password are wrong, please use the correct credentials and try it again"); // If it was found else { // Create a user object with the retrieved user id User userLogged = new User(int.Parse(lhResultset[0]["id_user"].ToString())); // Load in the outlook bar this.frmMain.vLoadOutlookBar(); // Display bar options based on profile this.frmMain.ShowUserOptions(lhResultset[0]["profile"].ToString()); // Pass the user to the main form this.frmMain.UserLogged = userLogged; // Close this form this.Close(); } }
/** * @desc Constructor * Loads in all fields from a single row of the ROOMS table. * @params [int] id_room identifies the room uniquely. * @return [none] No directly returned data. */ public Room(int id_room) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Launch the query to return all fields from a single row of the ROOMS table List<Hashtable> lhResultset = conn.lhSqlQuery("Select * from rooms WHERE id_room = '" + id_room + "'"); // Check if we found the room if ((int)lhResultset.Count > 0) { // Fill in all room fields with table data this.Id_room = int.Parse(lhResultset[0]["id_room"].ToString()); this.Size = int.Parse(lhResultset[0]["size"].ToString()); this.Description = lhResultset[0]["description"].ToString(); this.Name = lhResultset[0]["name"].ToString(); } }
/** * @desc Constructor. * Loads in various info from tables CLASSES, CLASS_INSTANCE and STAFF for this class instance. * Loads in all atendants for this class instance. * @params [int] id_class_instance identifies the class instance uniquely. * @return [none] No directly returned data. */ public ClassInstance(int id_class_instance) { // Create mysql connection. mySqlConn conn = new mySqlConn(); conn.connect(); // Launch the query to return all all fields from a single class instance row of the CLASS_INSTANCE table. List<Hashtable> lhResultSet = conn.lhSqlQuery("SELECT ci.id_class_instance, c.name, c.type, c.description, s.firstName, s.id_staff, DATE_FORMAT(ci.date, '%d/%m/%Y') date, ci.start_time, ci.end_time, ci.id_room, c.id_class, ci.frequency FROM classes c, class_instance ci, staff s WHERE ci.id_class = c.id_class AND ci.id_staff = s.id_staff AND ci.id_class_instance = '" + id_class_instance + "'"); // Check if we found the row if ((int)lhResultSet.Count > 0) { // Fill in all class instance fields with table data this.Id_class_instance = int.Parse(lhResultSet[0]["id_class_instance"].ToString()); this.Id_staff = int.Parse(lhResultSet[0]["id_staff"].ToString()); this.ClRoom = new Room(int.Parse(lhResultSet[0]["id_room"].ToString())); this.ClClass = new Class(int.Parse(lhResultSet[0]["id_class"].ToString())); this.DateStart = lhResultSet[0]["date"].ToString(); this.EndTime = lhResultSet[0]["end_time"].ToString(); this.StartTime = lhResultSet[0]["start_time"].ToString(); this.Frequency = lhResultSet[0]["frequency"].ToString(); // Create a list for storing member objects // Load in all records for the same class instance from CLASS_BOOKINGS table (each contains a different member ID) List<Hashtable> lhResultSetBookings = conn.lhSqlQuery("SELECT * FROM `gym`.`class_bookings` WHERE id_class_instance = '" + id_class_instance + "'"); // If there is any class booking (any member enrolled) exist with the class instance id if ((int)lhResultSetBookings.Count > 0) { // Create a list of attending members foreach (Hashtable hClassBooking in lhResultSetBookings) { // Retrieve the member number from the current class booking int id_member = int.Parse(hClassBooking["id_member"].ToString()); // Create a corresponding member object with all the particular member info loaded into it Member clMember = new Member(id_member); // If a member with this id_member actually exist, then add the member object to the list if(clMember.Id_member != -1) this.lclAttendants.Add(clMember); } } } }
/** * @desc Constructor * Loads in all fields from a single row of the USERS table. * @params [int] id_user identifies the user uniquely. * @return [none] No directly returned data. */ public User(int id_user) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create query string query = "SELECT * FROM users WHERE id_user = '******'"; // Launch the query to return all fields from a single row of the USERS table List<Hashtable> lhResultset = conn.lhSqlQuery(query); // Check the user was found if ((int)lhResultset.Count > 0) { // Fill in all user fields with table data this.Id_user = int.Parse(lhResultset[0]["id_user"].ToString()); this.IsActive = true; this.Login = lhResultset[0]["login"].ToString(); this.Password = lhResultset[0]["password"].ToString(); this.Profile = lhResultset[0]["profile"].ToString(); } else MessageBox.Show("The User could not be found!"); }
/** * @desc Constructor * Loads in all fields from a single "Gym Class" row of the CLASSES table. * @params [int] id_class identifies the class uniquely. * @return [none] No directly returned data. */ public Class(int id_class) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Launch the query to return all fields from a single "Gym Class" row of the CLASSES table List<Hashtable> lhResultSet = conn.lhSqlQuery("Select * from classes WHERE id_class = '" + id_class + "'"); // Check if we found the row if ((int)lhResultSet.Count > 0) { // Fill in all class fields with table data this.Id_class = int.Parse(lhResultSet[0]["id_class"].ToString()); this.Type = lhResultSet[0]["type"].ToString(); this.Description = lhResultSet[0]["description"].ToString(); this.Name = lhResultSet[0]["name"].ToString(); } }
/** * @desc Constructor * Loads in all fields from a single row of the EQUIPMENT table. * @params [int] id_equipment identifies the equipment uniquely. * @return [none] No directly returned data. */ public Equipment(int id_equipment) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Launch the query to return all fields from a single row of the EQUIPMENT table List<Hashtable> lhResultset = conn.lhSqlQuery("Select * from equipment WHERE id_equipment = '" + id_equipment + "'"); // Check if we found the equipment if ((int)lhResultset.Count > 0) { // Fill in all equipment fields with table data this.Id_equipment = int.Parse(lhResultset[0]["id_equipment"].ToString()); this.Type = lhResultset[0]["type"].ToString(); this.Id_vehicle = int.Parse(lhResultset[0]["id_vehicle"].ToString()); this.Name = lhResultset[0]["name"].ToString(); this.Description = lhResultset[0]["description"].ToString(); // Fill in all equipment set fields with table data if (this.Type == "set") { this.ItemInSet1 = int.Parse(lhResultset[0]["iteminset1"].ToString()); this.ItemInSet2 = int.Parse(lhResultset[0]["iteminset2"].ToString()); this.ItemInSet3 = int.Parse(lhResultset[0]["iteminset3"].ToString()); this.ItemInSet4 = int.Parse(lhResultset[0]["iteminset4"].ToString()); this.ItemInSet5 = int.Parse(lhResultset[0]["iteminset5"].ToString()); this.AmountInSet1 = int.Parse(lhResultset[0]["amountinset1"].ToString()); this.AmountInSet2 = int.Parse(lhResultset[0]["amountinset2"].ToString()); this.AmountInSet3 = int.Parse(lhResultset[0]["amountinset3"].ToString()); this.AmountInSet4 = int.Parse(lhResultset[0]["amountinset4"].ToString()); this.AmountInSet5 = int.Parse(lhResultset[0]["amountinset5"].ToString()); } } }
/** * @desc This method loads in every time the frm_class_instance_arrange is instantiated * @params [none] No input parameter. * @return [none] No directly returned data. */ private void frm_class_instance_arrange_Load(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Loading Available Classes string query = "SELECT id_class, name FROM classes ORDER BY id_class"; ArrayList myItems = conn.alGetComboFromDB(query, "id_class","name"); cmb_classes.DisplayMember = "Value"; cmb_classes.DataSource = myItems; // Loading Available Rooms query = "SELECT id_room, name FROM rooms ORDER BY id_room"; myItems = conn.alGetComboFromDB(query, "id_room", "name"); cmb_rooms.DisplayMember = "Value"; cmb_rooms.DataSource = myItems; // Loading Available Instructor //TODO: Inform Katie about the Position -> Instructor field query = "SELECT id_staff, CONCAT(lastName,', ', firstName) name FROM staff WHERE position = 'Instructor' ORDER BY id_staff"; myItems = conn.alGetComboFromDB(query, "id_staff", "name"); cmb_instructors.DisplayMember = "Value"; cmb_instructors.DataSource = myItems; // Copy the details of the class instancs to the form if (this.clClassInstance.Id_class_instance != -1) { cmb_classes.SelectedIndex = cmb_classes.FindString(clClassInstance.ClClass.Name); cmb_rooms.SelectedIndex = cmb_rooms.FindString(clClassInstance.ClRoom.Name); cmb_repeats.SelectedIndex = cmb_repeats.FindString(clClassInstance.Frequency); // Display number of members enlisted to this class instance query = "SELECT COUNT(*) q FROM gym.class_bookings WHERE id_class_instance = '" + this.clClassInstance.Id_class_instance + "'"; List<Hashtable> lhRes = conn.lhSqlQuery(query); lbl_currentmembers_amount.Text = lhRes[0]["q"].ToString(); // Display max members allowed in the room allocated to this class query = "SELECT r.size FROM gym.class_instance ci, gym.rooms r WHERE ci.id_room = r.id_room AND ci.id_class_instance = '" + this.clClassInstance.Id_class_instance + "'"; lhRes = conn.lhSqlQuery(query); label_maxmembers_amount.Text = lhRes[0]["size"].ToString(); } else { // If the class instance was not found, it is not possible to assign member or equipment to it button_equipmentbooking.Enabled = false; button_viewattendants.Enabled = false; button_enrollmembers.Enabled = false; button_remove.Enabled = false; button_equipmentbooking.Enabled = false; } }
/** * @desc Update the max members allowed in room based on currently selected room * @params [none] No input parameter. * @return [none] No directly returned data. */ private void cmb_rooms_SelectedIndexChanged(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Get room id DictionaryEntry de = (DictionaryEntry)cmb_rooms.SelectedItem; string id_room = de.Key.ToString(); // Get room size based on room id string query = "SELECT size FROM gym.rooms WHERE id_room = '" + id_room + "'"; List<Hashtable> lhRes = conn.lhSqlQuery(query); // Display new size label_maxmembers_amount.Text = lhRes[0]["size"].ToString(); }
/** * @desc Constructor * Loads in all fields from a single row of the MEMBERS table. * @params [int] id_member identifies the member uniquely. * @return [none] No directly returned data. */ public Member(int id_member) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Launch the query to return all fields from a single row of the MEMBERS table List<Hashtable> lhResultset = conn.lhSqlQuery("Select * from members m, users u where u.id_user = m.id_user AND m.id_member = '" + id_member + "'"); // Check if we found the member if ((int)lhResultset.Count > 0) { // Fill in all member and parent user fields with table data this.clUser = new User(); this.clUser.Id_user = int.Parse(lhResultset[0]["id_user"].ToString()); this.clUser.Login = lhResultset[0]["login"].ToString(); this.clUser.Password = lhResultset[0]["password"].ToString(); this.clUser.Profile = lhResultset[0]["profile"].ToString(); this.IsActive = (lhResultset[0]["is_active"].ToString() == "True") ? true : false; this.Id_member = int.Parse(lhResultset[0]["id_member"].ToString()); this.Address_2 = lhResultset[0]["address_2"].ToString(); this.Address_1 = lhResultset[0]["address_1"].ToString(); this.Birthdate = lhResultset[0]["birthdate"].ToString(); this.City = lhResultset[0]["city"].ToString(); this.County = lhResultset[0]["county"].ToString(); this.Email = lhResultset[0]["email"].ToString(); this.EmergContactMobile = lhResultset[0]["emerg_contact_mobile"].ToString(); this.EmergContactName = lhResultset[0]["emerg_contact_name"].ToString(); this.EmergContactPhone = lhResultset[0]["emerg_contact_phone"].ToString(); this.EmergContactRelation = lhResultset[0]["emerg_contact_relation"].ToString(); this.FirstName = lhResultset[0]["firstName"].ToString(); this.LastName = lhResultset[0]["lastName"].ToString(); this.MedicalAllergies = lhResultset[0]["medical_allergies"].ToString(); this.MedicalDoctorName = lhResultset[0]["medical_doctor_name"].ToString(); this.MedicalNotes = lhResultset[0]["medical_notes"].ToString(); this.MedicalPhone = lhResultset[0]["medical_phone"].ToString(); this.MemberNumber = lhResultset[0]["member_number"].ToString(); this.Id_file = lhResultset[0]["id_file"].ToString(); this.PostalCode = lhResultset[0]["postalcode"].ToString(); this.Type = lhResultset[0]["type"].ToString(); this.Mobile = lhResultset[0]["mobile"].ToString(); this.Phone = lhResultset[0]["phone"].ToString(); this.Gender = lhResultset[0]["gender"].ToString(); } }
/** * @desc Method for checking if there is any overlap of class instances in the same room or same instructor at conflicting times * @params [string] sDate has the date of the class instance * @params [string] id_room is the room where the class instance takes place * @params [string] id_staff is the instructor on this occasion * @params [string] sStartTime is the start time * @params [string] sEndTime is the end time * @return [bool] Returns true if there is an overlap and false if everything is green ligth */ public bool bCheckOverlap(string date, string id_room, string id_staff, string dtartTime, string endTime) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create the overlap check query string query = "SELECT * FROM gym.class_instance WHERE date = '" + date + "' AND (id_room = '" + id_room + "' OR id_staff = '" + id_staff + "') AND (" + "(start_time BETWEEN '" + startTime + "' AND '" + endTime + "') OR " + "(end_time BETWEEN '" + startTime + "' AND '" + endTime + "') OR " + "(start_time < '" + startTime + "' AND end_time > '" + endTime + "') OR " + "(start_time > '" + startTime + "' AND end_time < '" + endTime + "'))" + ((this.Id_class_instance != -1)?" AND id_class_instance != '"+this.Id_class_instance+"'":""); // Launch the overlap check query and load the result into a hashtable List<Hashtable> lhResultSet = conn.lhSqlQuery(query); // If there is any result then there is an overlap if ((int)lhResultSet.Count >= 1) return true; // Otherwise ther is no overlap return false; }
/** * @desc Executes when a grid cell is double clicked on the member list * It loads in the member belonging to the cell for editing * @params [none] No input parameter. * @return [none] No directly returned data. */ private void dg_members_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { try { // Get the member id at current cell int id_member = int.Parse(dg_members.Rows[e.RowIndex].Cells[0].Value.ToString()); // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // If this member list was launched from the payment list panel if (this.frmPayments != null) { // Launch Add Payment panel for the selected member frm_add_payment frmAddPayment = new frm_add_payment(id_member); frmAddPayment.ShowDialog(); // As this member list was launched from the payment list, refresh the payment list when this finishes this.frmPayments.vloadDgPayments(); this.Close(); return; } // If this member list was launched from class instance list for adding new members if (this.clClassInstance.Id_class_instance != -1 && this.viewAttendants == false) { // Confirm enrollment DialogResult dialogResult = MessageBox.Show("Enroll this member to the class?", "Enroll member?", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dialogResult == DialogResult.Yes) { // Check the room size string query = "SELECT COUNT(*) q FROM gym.class_bookings WHERE id_class_instance = '" + this.clClassInstance.Id_class_instance + "'"; List<Hashtable> lhRes = conn.lhSqlQuery(query); int currMembers = int.Parse(lhRes[0]["q"].ToString()); query = "SELECT r.size FROM gym.class_instance ci, gym.rooms r WHERE ci.id_room = r.id_room AND ci.id_class_instance = '" + this.clClassInstance.Id_class_instance + "'"; lhRes = conn.lhSqlQuery(query); int maxMembers = int.Parse(lhRes[0]["size"].ToString()); if (maxMembers < currMembers + 1) { MessageBox.Show("Sorry! This room does not allow more bookings!"); return; } Member clMember = new Member(id_member); this.clClassInstance.LclAttendants.Add(clMember); this.clClassInstance.SaveClassInstance(); } } // If this member list was launched from class instance list for viewing attending members if (this.clClassInstance.Id_class_instance != -1 && this.viewAttendants == true) { // Confirm removal DialogResult dialogResult = MessageBox.Show("Remove this member from the class?", "Delete entry?", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dialogResult == DialogResult.Yes) { // Create delete query string deleteClassBookingQuery = "delete from class_bookings WHERE id_member = '" + id_member + "'" + " AND id_class_instance = '" + this.clClassInstance.Id_class_instance + "'"; // Launch delete query int result = conn.DeleteOrUpdate(deleteClassBookingQuery); // Check delete result if (result > 0) { MessageBox.Show("The attendant has been removed from this class instance!"); vLoadMemberList(); } else { MessageBox.Show("There was a problem updating the class booking information, please check your data!"); return; } } } // If this member list was launched from main menu just create an edit member form else { frm_member frmMember = new frm_member(id_member, this); frmMember.ShowDialog(); } }catch(Exception) { return; } }