/** * @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 This method refreshes the room list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadRoomList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query for retrieving all rooms string query = "SELECT id_room RID, name Name, size Size, description Description FROM rooms ORDER BY id_room"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_rooms.DataSource = itemsSource; dg_rooms.AllowUserToAddRows = false; dg_rooms.ReadOnly = true; }
/** * @desc This method refreshes the equipment booking list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadBookedList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query string query = "SELECT eb.id_eq_booking BookingNr, eb.id_equipment EqID, e.name Equipment, eb.borrowedamount Amount, eb.id_member MemberID, CONCAT(m.lastName, ', ', m.firstName) MemberName, eb.id_staff StaffID, CONCAT(s.lastName, ', ', s.firstName) SaffName, eb.id_class_instance ClassID, eb.date_start, eb.date_due FROM equipment e, equipment_bookings eb LEFT OUTER JOIN staff s ON eb.id_staff = s.id_staff LEFT OUTER JOIN members m ON eb.id_member = m.id_member WHERE e.id_equipment = eb.id_equipment AND ((eb.isreturned IS NULL) OR (eb.isreturned = 0)) ORDER BY e.name"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_eqbookings.DataSource = itemsSource; dg_eqbookings.AllowUserToAddRows = false; dg_eqbookings.ReadOnly = true; }
/** * @desc This method refreshes the class instance list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadDgClassList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query string query = "SELECT ci.id_class_instance CID, c.name Name, c.type Type, c.description Description, s.firstName Instructor, DATE_FORMAT(ci.date, '%d/%m/%Y') Date, ci.start_time Start, ci.end_time End FROM classes c, class_instance ci, staff s WHERE ci.id_class = c.id_class AND ci.id_staff = s.id_staff ORDER BY ci.date, ci.start_time"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_classes.DataSource = itemsSource; dg_classes.AllowUserToAddRows = false; dg_classes.ReadOnly = true; }
/** * @desc This method refreshes the class list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadClassList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query string query = "SELECT id_class CID, name Name, type Type, description Description FROM classes ORDER BY id_class"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_classes.DataSource = itemsSource; dg_classes.AllowUserToAddRows = false; dg_classes.ReadOnly = true; }
/** * @desc This method refreshes the staff list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadStaffList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query for retrieving all staff string query = "SELECT id_staff MID, firstName as 'First Name', lastName 'Last Name', DATE_FORMAT(birthdate,\"%d/%m/%Y\") DOB, email 'EMail' FROM staff s, users u WHERE u.id_user = s.id_user ORDER BY id_staff"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_staff.DataSource = itemsSource; dg_staff.AllowUserToAddRows = false; dg_staff.ReadOnly = true; }
/** * @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 Executes when the "Search" button is clicked * It creates a new list of class instances based on search criteria given by the user * @params [none] No input parameter. * @return [none] No directly returned data. */ private void button_search_Click(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create start of query string query = "SELECT ci.id_class_instance CID, c.name Name, c.type Type, c.description Description, s.firstName Instructor, DATE_FORMAT(ci.date, '%d/%m/%Y') Date, ci.start_time Start, ci.end_time End FROM classes c, class_instance ci, staff s WHERE ci.id_class = c.id_class AND ci.id_staff = s.id_staff "; // Check user input and create query for search if (txt_classname.Text != "") query += " AND c.name LIKE '%" + txt_classname.Text + "%'"; query += " ORDER BY ci.date, ci.start_time"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_classes.DataSource = itemsSource; dg_classes.AllowUserToAddRows = false; dg_classes.ReadOnly = true; }
/** * @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 Executes when the Search button is clicked * It creates a new list of rooms based on search criteria given by the user * @params [none] No input parameter. * @return [none] No directly returned data. */ private void button_search_Click(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create start of query string query = "SELECT id_room RID, name Name, size Size, description Description FROM rooms WHERE 1 = 1"; // Check user input and create query for search if (txt_roomname.Text != "") query += " AND name LIKE '%" + txt_roomname.Text + "%'"; query += " ORDER BY id_room"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_rooms.DataSource = itemsSource; dg_rooms.AllowUserToAddRows = false; dg_rooms.ReadOnly = true; }
/** * @desc This method refreshes the payment list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vloadDgPayments() { string query = ""; // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query for retrieving all payments if (this.Id_member == -1) query = "SELECT m.id_member MID, m.member_number NO, CONCAT(m.lastName,', ', m.firstName) Name, p.amount Amount, p.details Details, DATE_FORMAT(p.date,'%d-%m-%Y') 'Date dd-mm-yyyy', CONCAT(s.lastName,', ', s.firstName) 'Received By', p.receiptnumber 'Receipt Number', p.paymentmethod Method FROM members m, payments p, staff s WHERE m.id_member = p.id_member AND s.id_staff = p.receivedby ORDER BY m.id_member"; // Create query for retrieving payments for a specific member only else query = "SELECT m.id_member MID, m.member_number NO, CONCAT(m.lastName,', ', m.firstName) Name, p.amount Amount, p.details Details, DATE_FORMAT(p.date,'%d-%m-%Y') 'Date dd-mm-yyyy', CONCAT(s.lastName,', ', s.firstName) 'Received By', p.receiptnumber 'Receipt Number', p.paymentmethod Method FROM members m, payments p, staff s WHERE m.id_member = p.id_member AND m.id_member = " + this.Id_member + " AND s.id_staff = p.receivedby ORDER BY m.id_member"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_payments.DataSource = itemsSource; dg_payments.AllowUserToAddRows = false; dg_payments.ReadOnly = true; }
/** * @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!"); }
private bool saveClick() { // create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Copy form field contents into class instance object fields string sDate = Utils.sGetMysqlDate(txt_startdate.Text); DictionaryEntry de = (DictionaryEntry)cmb_classes.SelectedItem; string id_class = de.Key.ToString(); de = (DictionaryEntry)cmb_instructors.SelectedItem; string id_staff = de.Key.ToString(); de = (DictionaryEntry)cmb_rooms.SelectedItem; string id_room = de.Key.ToString(); // Check user inputs and formats if(sDate == "0000-00-00") { MessageBox.Show("Check the date format!"); return false; }else if(Utils.bValidateTime(txt_starttime.Text) == false || Utils.bValidateTime(txt_starttime.Text) == false) { MessageBox.Show("Check the time format!"); return false; } else if (cmb_classes.Text == "" || cmb_instructors.Text == "" || cmb_rooms.Text == "") { MessageBox.Show("Missing information to arrange your class. Please check input data."); return false; } // Check if the user was found if (this.clClassInstance.bCheckOverlap(sDate, id_room, id_staff, txt_starttime.Text, txt_endtime.Text)) MessageBox.Show("The class is overlapping with another class, please specify another date, room or instructor."); else { this.clClassInstance.Id_staff = int.Parse(id_staff); this.clClassInstance.ClRoom = new Room(int.Parse(id_room)); this.clClassInstance.ClClass = new Class(int.Parse(id_class)); this.clClassInstance.DateStart = sDate; this.clClassInstance.EndTime = txt_endtime.Text; this.clClassInstance.Frequency = cmb_repeats.Text; this.clClassInstance.StartTime = txt_starttime.Text; if (this.clClassInstance.SaveClassInstance()) return true; } return false; }
/** * @desc It sets up the form for displaying details of a set * @params [none] No input parameter. * @return [none] No directly returned data. */ private void rd_set_Checked() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Loading Available Items string query = "SELECT id_equipment, name FROM equipment WHERE type = 'item' ORDER BY id_equipment"; ArrayList myItems1 = conn.alGetComboFromDB(query, "id_equipment", "name"); ArrayList myItems2 = conn.alGetComboFromDB(query, "id_equipment", "name"); ArrayList myItems3 = conn.alGetComboFromDB(query, "id_equipment", "name"); ArrayList myItems4 = conn.alGetComboFromDB(query, "id_equipment", "name"); ArrayList myItems5 = conn.alGetComboFromDB(query, "id_equipment", "name"); // Create an option for "none" in each combobox's first position DictionaryEntry dent = (DictionaryEntry)myItems1[0]; dent.Key = "0"; dent.Value = "none"; myItems1.Insert(0, dent); myItems2.Insert(0, dent); myItems3.Insert(0, dent); myItems4.Insert(0, dent); myItems5.Insert(0, dent); // Assign sources and displaymembers to all comboboxes cmb_item1.DisplayMember = "Value"; cmb_item1.DataSource = myItems1; cmb_item2.DisplayMember = "Value"; cmb_item2.DataSource = myItems2; cmb_item3.DisplayMember = "Value"; cmb_item3.DataSource = myItems3; cmb_item4.DisplayMember = "Value"; cmb_item4.DataSource = myItems4; cmb_item5.DisplayMember = "Value"; cmb_item5.DataSource = myItems5; // Find the position of the item in the source and set this to be the selected index // in each combobox int j = 0; foreach (object o in myItems1) { dent = (DictionaryEntry)myItems1[j]; if (int.Parse(dent.Key.ToString()) == clEquipment.ItemInSet1) cmb_item1.SelectedIndex = j; j++; } j = 0; foreach (object o in myItems1) { dent = (DictionaryEntry)myItems1[j]; if (int.Parse(dent.Key.ToString()) == clEquipment.ItemInSet2) cmb_item2.SelectedIndex = j; j++; } j = 0; foreach (object o in myItems1) { dent = (DictionaryEntry)myItems1[j]; if (int.Parse(dent.Key.ToString()) == clEquipment.ItemInSet3) cmb_item3.SelectedIndex = j; j++; } j = 0; foreach (object o in myItems1) { dent = (DictionaryEntry)myItems1[j]; if (int.Parse(dent.Key.ToString()) == clEquipment.ItemInSet4) cmb_item4.SelectedIndex = j; j++; } j = 0; foreach (object o in myItems1) { dent = (DictionaryEntry)myItems1[j]; if (int.Parse(dent.Key.ToString()) == clEquipment.ItemInSet5) cmb_item5.SelectedIndex = j; j++; } // Display counter values on form counter_item1.Value = clEquipment.AmountInSet1; counter_item2.Value = clEquipment.AmountInSet2; counter_item3.Value = clEquipment.AmountInSet3; counter_item4.Value = clEquipment.AmountInSet4; counter_item5.Value = clEquipment.AmountInSet5; // Display remaining set related settings on form label_iteminfo.Hide(); label_itemsetinfo.Show(); label_itemname.Enabled = false; txt_itemname.Enabled = false; label_setname.Enabled = true; txt_setname.Enabled = true; label_itemdesc.Hide(); label_itemsetdesc.Show(); label_iteminset.Enabled = true; label_itemamount.Enabled = true; cmb_item1.Enabled = true; if (cmb_item1.SelectedIndex < 1) cmb_item2.Enabled = false; if (cmb_item2.SelectedIndex < 1) cmb_item3.Enabled = false; if (cmb_item3.SelectedIndex < 1) cmb_item4.Enabled = false; if (cmb_item4.SelectedIndex < 1) cmb_item5.Enabled = false; if (cmb_item1.SelectedIndex < 1) counter_item2.Enabled = false; if (cmb_item2.SelectedIndex < 1) counter_item3.Enabled = false; if (cmb_item3.SelectedIndex < 1) counter_item4.Enabled = false; if (cmb_item4.SelectedIndex < 1) counter_item5.Enabled = false; }
/** * @desc This method will update the password for the user in the USERS table * @params [int] id_user identifies the user uniquely. * @params [string] oldPassword: Old password of the user * @params [string] newPassword: The new password of the user * @return [bool] Returns true in case of success, false if there was problem updating the password */ public bool UpdatePassword(int id_User, string oldPassword, string newPassword) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create update password query string query = "UPDATE users SET password = MD5('" + newPassword + "') WHERE id_user = '******' AND password = MD5('" + oldPassword + "')"; // Launch update password query int result = conn.DeleteOrUpdate(query); // Check update result if (result > 0) return true; else return false; }
/** * @desc This method will save or update a user in the USERS table * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem saving/updating the user */ public bool SaveUser() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // If this is a new user if (this.Id_user == -1) { // Create insert query string query = "insert into users (id_user, login, password, profile, active) " + "values (NULL, '" + this.Login + "', MD5('" + this.Password + "'), '" + this.Profile + "', '" + ((this.isActive) ? "1" : "0") + "')"; // Launch insert query this.id_user = conn.InsertToDB(query); // Check result if (this.id_user > 0) return true; } // This is an existing user else { // Create update query string query = "UPDATE users SET login = '******', active = '" + ((this.isActive) ? "1" : "0") + "' "+ "WHERE id_user = '******'"; // Launch update query int result = conn.DeleteOrUpdate(query); // Check update result if (result > 0) return true; } return false; }
/** * @desc Loads in when the object instance first instantiated * @params [none] No input parameter. * @return [none] No directly returned data. */ private void frm_add_payment_Load(object sender, EventArgs e) { // Store todays date DateTime today = DateTime.Today; // Convert date to display format txt_date.Text = String.Format("{0:dd-MM-yyyy}", today); // Loading Available Staff //TODO: Inform Katie about the Position -> Instructor field // Create mysl connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create query for returning all staff for selection of payment "received by" string query = "SELECT id_staff, CONCAT(lastName,', ', firstName) name FROM staff ORDER BY lastName"; // Load result into an arraylist ArrayList myItems = conn.alGetComboFromDB(query, "id_staff", "name"); // Set combobox to display value cmb_staff.DisplayMember = "Value"; // Set myItems a combobox source cmb_staff.DataSource = myItems; // Set combobox index to be -1 so it can be checked if the user won't select anything here // TODO: SET THIS TO BE A CONSTANT AS THE CURRENTLY LOGGED IN STAFF?? cmb_staff.SelectedIndex = -1; }
/** * @desc Removes the class from the CLASSES table. * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem deleting the class. */ public bool RemoveClass() { // Check if there is a class already loaded in if (this.Id_class != -1) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create the delete query string query = "DELETE FROM classes WHERE id_class = '" + this.Id_class + "'"; // Launch delete query int result = conn.DeleteOrUpdate(query); // Check deletion result if (result > 0) { MessageBox.Show("The class data has been deleted succesfully!"); return true; } else { MessageBox.Show("There was a problem deleting the class!"); return false; } } return false; }
/** * @desc This method refreshes the equipment list * @params [string] type: this tells to method to list items or sets * @return [none] No directly returned data. */ public void vLoadEqList(string type) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query for retrieving item or set depending on parameter string query = "SELECT id_equipment EID, name Name, description Description FROM equipment WHERE type = '"+type+"' ORDER BY id_equipment"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_equipment.DataSource = itemsSource; dg_equipment.AllowUserToAddRows = false; dg_equipment.ReadOnly = true; }
/** * @desc This method refreshes the booked equipment list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadBookedList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query string query = "SELECT DISTINCT eb.date_due Due, e.name Name, eb.borrowedamount Amount, eb.id_equipment EqID, eb.id_eq_booking BkID FROM equipment e, equipment_bookings eb WHERE eb.id_class_instance = " + clClassInstance.Id_class_instance + " AND (eb.isreturned = 0 OR eb.isreturned is NULL) AND eb.id_equipment = e.id_equipment ORDER BY Due"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_eqbookings.DataSource = itemsSource; dg_eqbookings.AllowUserToAddRows = false; dg_eqbookings.ReadOnly = true; }
/** * @desc This method refreshes the booked equipment list * @params [none] No input parameter. * @return [none] No directly returned data. */ public void vLoadBookedList() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query string query = "SELECT DISTINCT eb.date_due Due, e.name Name, eb.borrowedamount Amount, eb.id_equipment EqID, eb.id_eq_booking BkID FROM equipment e, equipment_bookings eb WHERE eb.id_staff = " + clStaff.Id_staff + " AND (eb.isreturned = 0 OR eb.isreturned is NULL) AND eb.id_equipment = e.id_equipment ORDER BY Due"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_eqbookings.DataSource = itemsSource; dg_eqbookings.AllowUserToAddRows = false; dg_eqbookings.ReadOnly = true; // Check for unreturned items, if there is any, then report ir to the user! if (dg_eqbookings.RowCount > 0) { int lateItems = 0; int rowIndex; for (rowIndex = 0; rowIndex < dg_eqbookings.RowCount; rowIndex++) { string eqDueDate = dg_eqbookings.Rows[rowIndex].Cells[0].Value.ToString(); DateTime today = DateTime.Today; DateTime due = DateTime.Parse(eqDueDate); int result = DateTime.Compare(today, due); if (result > 0) lateItems++; } if (lateItems > 0) MessageBox.Show("This persom has " + lateItems + " late, unreturned item(s)."); } }
/** * @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 will save or update an equipment in the EQUIPMENT table * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem saving/updating the equipment */ public bool SaveEquipment() { string saveEquipmentQuery; // Checking user input if (this.Name == "") { MessageBox.Show("Please Insert a name."); } else { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Check whether there is a new id_equipment assigned to this equipment, // if not then this a new equipment to save if (this.Id_equipment == -1) { // Create the save query saveEquipmentQuery = "insert into `gym`.`equipment` (`id_equipment`, `type`, `id_vehicle`, `name`, `description`, `iteminset1`, `iteminset2`, `iteminset3`, `iteminset4`, `iteminset5`, `amountinset1`, `amountinset2`, `amountinset3`, `amountinset4`, `amountinset5`) values " + "(NULL, '" + this.Type + "', '" + this.Id_vehicle + "', '" + this.Name + "', '" + this.Description + "', '" + this.ItemInSet1 + "', '" + this.ItemInSet2 + "', '" + this.ItemInSet3 + "', '" + this.ItemInSet4 + "', '" + this.ItemInSet5 + "', '" + this.AmountInSet1 + "', '" + this.AmountInSet2 + "', '" + this.AmountInSet3 + "', '" + this.AmountInSet4 + "', '" + this.AmountInSet5 + "')"; // Launch save query int id_equipment = conn.InsertToDB(saveEquipmentQuery); // Check saving result if (id_equipment != -1) { this.Id_equipment = id_equipment; MessageBox.Show("The new equipment has been added to the databse succesfully!"); return true; } else { MessageBox.Show("There was a problem adding the new equipment, please check your data!"); return false; } } // If an id_equipment already exists for this equipment, then this is an existing equipment to update else { // Create update query string updateEquimentQuery = "UPDATE equipment SET type = '" + this.Type + "', id_vehicle = '" + this.Id_vehicle + "', name = '" + this.Name + "', description = '" + this.Description + "', iteminset1 = '" + this.ItemInSet1 + "', iteminset2 = '" + this.ItemInSet2 + "', iteminset3 = '" + this.ItemInSet3 + "', iteminset4 = '" + this.ItemInSet4 + "', iteminset5 = '" + this.ItemInSet5 + "', amountinset1 = '" + this.AmountInSet1 + "', amountinset2 = '" + this.AmountInSet2 + "', amountinset3 = '" + this.AmountInSet3 + "', amountinset4 = '" + this.AmountInSet4 + "', amountinset5 = '" + this.AmountInSet5 + "' " + " WHERE id_equipment = '" + this.Id_equipment + "'"; // Launch update query int result = conn.DeleteOrUpdate(updateEquimentQuery); // Check update result if (result > 0) { MessageBox.Show("The equipment data has been updated succesfully!"); return true; } else { MessageBox.Show("There was a problem updating the equipment information, please check your data!"); return false; } } } return false; }
/** * @desc Removes the equipment from the EQUIPMENT table. * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem deleting the class. */ public bool RemoveEquipment() { if (this.Id_equipment != -1) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create the delete query string query = "DELETE FROM equipment WHERE id_equipment = '" + this.Id_equipment + "'"; // Launch delete query int result = conn.DeleteOrUpdate(query); // Check deletion result if (result > 0) { MessageBox.Show("The equipment data has been deleted succesfully!"); return true; } else { MessageBox.Show("There was a problem deleting the equipment!"); return false; } } return false; }
/** * @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 Executes when the "Search" button is clicked * It creates a new list of equipments based on search criteria given by the user * @params [none] No input parameter. * @return [none] No directly returned data. */ private void button_search_Click(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create query different one for either set or item depending on settings done by user on form string query = ""; if (rd_item.Checked == true) query = "SELECT id_equipment EID, name Name, description Description FROM equipment WHERE type = 'item' AND 1 = 1"; else query = "SELECT id_equipment EID, name Name, description Description FROM equipment WHERE type = 'set' AND 1 = 1"; // Check user input and create query for search if (txt_equipmentname.Text != "") query += " AND name LIKE '%" + txt_equipmentname.Text + "%'"; query += " ORDER BY name"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_equipment.DataSource = itemsSource; dg_equipment.AllowUserToAddRows = false; dg_equipment.ReadOnly = true; }
/** * @desc This method will save or update a class in the CLASS table * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem saving/updating the class */ public bool SaveClass() { string query; // Checking user input if (this.Name == "") { MessageBox.Show("Please Insert a name."); } else { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Check whether there is a new id_class assigned to this class, // if not then this a new class to save if (this.Id_class == -1) { // Create the save query query = "insert into `gym`.`classes` (`id_class`, `name`, `type`, `description`) values " + "(NULL, '" + this.Name + "', '" + this.Type + "', '" + this.Description + "')"; // Launch save query int id_class = conn.InsertToDB(query); // Check saving result if (id_class != -1) { this.Id_class = id_class; MessageBox.Show("The new class has been added to the databse succesfully!"); return true; } else { MessageBox.Show("There was a problem adding the new class, please check your data!"); return false; } } // If an id_class already exists for this class instance, then this is an existing class to update else { // Create update query query = "UPDATE classes SET name = '" + this.Name + "', description = '" + this.Description + "' " + " WHERE id_class = '" + this.Id_class + "'"; // Launch update query int result = conn.DeleteOrUpdate(query); // Check update result if (result > 0) { MessageBox.Show("The class data has been updated succesfully!"); return true; } else { MessageBox.Show("There was a problem updating the class information, please check your data!"); return false; } } } return false; }
/** * @desc Removes the user from the USERS table. * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem deleting the user. */ public bool DeleteUser() { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create delete query string query = "DELETE FROM users WHERE id_user = '******'"; // Check delet query result int result = conn.DeleteOrUpdate(query); if (result > 0) return true; return false; }
/** * @desc This method will save or update an equipment booking in the EQUIPMENT_BOOKINGS table * @params [none] No input parameter. * @return [bool] Returns true in case of success, false if there was problem saving/updating the equipment */ public bool SaveEquipmentBooking() { string query; // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Check whether there is a new id_eq_booking assigned to this booking, // if not then this a new equipment booking to save if (this.Id_eq_booking == -1) { // Create the save query query = "insert into `gym`.`equipment_bookings` (`id_eq_booking`, `id_staff`, `id_member`, `id_class_instance`, `date_start`, `date_due`, `id_equipment`, `borrowedamount`,`isreturned`) values " + "(NULL, " + this.Id_staff + ", " + this.Id_member + ", " + this.Id_class_instance + ", '" + this.DateStart + "', '" + this.DateDue + "', " + this.Id_equipment + ", " + this.BorrowedAmount + ", NULL)"; // Launch save query int id_eq_booking = conn.InsertToDB(query); // Check saving result if (id_eq_booking != -1) { this.Id_eq_booking = id_eq_booking; MessageBox.Show("The new equipment booking has been added to the databse succesfully!"); return true; } else { MessageBox.Show("There was a problem adding the new equipment booking, please check your data!"); return false; } } // If an id_eq_booking already exists for this booking, then this is an existing booking to update else { // Create update query query = "UPDATE `gym`.`equipment_bookings` SET `borrowedamount` = " + this.BorrowedAmount + ", `isreturned`= " + this.IsReturned + " WHERE id_eq_booking = '" + this.Id_eq_booking + "'"; // Launch update query int result = conn.DeleteOrUpdate(query); // Check update result if (result > 0) { MessageBox.Show("The equipment booking data has been updated succesfully!"); return true; } else { MessageBox.Show("There was a problem updating the equipment booking information, please check your data!"); return false; } } //return true; }
/** * @desc Executes when the Search button is clicked * It creates a new list of staff based on search criteria given by the user * @params [none] No input parameter. * @return [none] No directly returned data. */ private void button_search_Click(object sender, EventArgs e) { // Create mysql connection mySqlConn conn = new mySqlConn(); conn.connect(); // Create source for grid BindingSource itemsSource = new BindingSource(); // Create start of query string query = "SELECT id_staff MID, staff_number NO, firstName as 'First Name', lastName 'Last Name', DATE_FORMAT(birthdate,\"%d/%m/%Y\") DOB, email 'EMail' FROM staffs WHERE 1 = 1 "; // Check user input and create query for search if (txt_firstName.Text != "") query += " AND firstName LIKE '%" + txt_firstName.Text + "%'"; if (txt_lastName.Text != "") query += " AND lastName LIKE '%" + txt_lastName.Text + "%'"; if (txt_email.Text != "") query += " AND email LIKE '%" + txt_email.Text + "%'"; string sDate = Utils.sGetMysqlDate(txt_dob.Text); if (sDate != "0000-00-00") query += " AND birthdate = '" + sDate + "'"; query += " ORDER BY id_staff"; // Launch query and load result into source itemsSource.DataSource = conn.dtGetTableForDataGrid(query); // Assign source to grid dg_staff.DataSource = itemsSource; dg_staff.AllowUserToAddRows = false; dg_staff.ReadOnly = true; }