/**
* @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 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 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 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 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 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 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 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 This method will save the object into the database
* @return [bool] Returns true in case of success, false if there was a problem
*/
public bool SaveMember()
{
// Convert date into mysql format
string mysqlDate = Utils.sGetMysqlDate(this.Birthdate);
string query;
// Check Birthdate format
if (mysqlDate == "0000-00-00")
{
MessageBox.Show("The Date of Birth is in incorrect format!");
}
// Check e-mail format
else if (Utils.bValidateEmail(this.Email) == false)
{
MessageBox.Show("The E-Mail address is incorrect!");
}
else
{
// First the user object is filled
clUser.IsActive = (this.IsActive) ? true : false;
clUser.Login = this.Email;
clUser.Password = mysqlDate;
clUser.Profile = "member";
// Create mysql connection
mySqlConn conn = new mySqlConn();
conn.connect();
// If the User details were correctly saved
if (clUser.SaveUser())
{
// Check if there is a new picture to save
if ((this.FilePath != null) && (this.FilePath.Length > 1))
{
this.Id_file = conn.uploadFileToDB(this.FilePath, this.FileName);
}
// The insert query is launched in case of existing members only, not new members
if (this.Id_member == -1)
{
// Create insert query
query = "insert into `gym`.`members` (`id_member`, `firstName`, `lastName`, `birthdate`, `address_1`, `city`, `county`, `postalcode`, `type`, `id_user`, `is_active`, `address_2`, `emerg_contact_name`, `emerg_contact_relation`, `emerg_contact_phone`, `emerg_contact_mobile`, `medical_allergies`, `medical_notes`, `id_file`, `medical_doctor_name`, `medical_phone`, `email`, `member_number`, `phone`,`mobile`,`gender`) values " +
"(NULL, '" + this.FirstName + "', '" + this.LastName + "', '" + mysqlDate + "', '" + this.Address_1 + "', '" + this.City + "', '" + this.County + "', '" + this.PostalCode + "', '" + this.Type + "', '" + clUser.Id_user + "', '" + ((this.IsActive) ? "1" : "0") + "', '" + this.Address_2 + "', '" + this.EmergContactName + "', '" + this.EmergContactRelation + "', '" + this.EmergContactPhone + "', '" + this.EmergContactMobile + "', '" + this.MedicalAllergies + "', '" + this.MedicalNotes + "', '" + this.Id_file + "', '" + this.MedicalDoctorName + "', '" + this.MedicalPhone + "', '" + this.Email + "', '" + this.MemberNumber + "','" + this.Phone + "','" + this.Mobile + "','" + this.Gender + "')";
// Launch insert query
int id_member = conn.InsertToDB(query);
// Check if the insert was successful
if (id_member != -1)
{
this.Id_member = id_member;
MessageBox.Show("The new member has been added to the databse succesfully!");
return true;
}
else
{
MessageBox.Show("There was a problem adding the new user, please check your data!");
clUser.DeleteUser();
return false;
}
}
// This is a member update
else
{
// Create update query
query = "UPDATE members SET firstName = '" + this.FirstName + "', lastName = '" + this.LastName + "', birthdate = '" + mysqlDate + "', address_1 = '" + this.Address_1 + "', city = '" + this.City + "', county = '" + this.County + "', postalcode = '" + this.PostalCode + "', type = '" + this.Type + "', is_active = " + ((this.IsActive) ? "1" : "0") + ", address_2 = '" + this.Address_2 + "', emerg_contact_name = '" + this.EmergContactName + "', emerg_contact_relation = '" + this.EmergContactRelation + "', emerg_contact_phone = '" + this.EmergContactPhone + "', emerg_contact_mobile = '" + this.EmergContactMobile + "', medical_allergies = '" + this.MedicalAllergies + "', medical_notes = '" + this.MedicalNotes + "', id_file = '" + this.Id_file + "', medical_doctor_name = '" + this.MedicalDoctorName + "', medical_phone = '" + this.MedicalPhone + "', email = '" + this.Email + "', phone = '" + this.Phone + "', mobile = '" + this.Mobile +
"', gender = '" + this.Gender + "' " + " WHERE id_member = '"+this.Id_member+"'";
// Launch update query
int result = conn.DeleteOrUpdate(query);
// Check if the update was successful
if (result > 0)
{
MessageBox.Show("The member data has been updated succesfully!");
return true;
}
else
{
MessageBox.Show("There was a problem updating the user information, please check your data!");
clUser.DeleteUser();
return false;
}
}
}
// If the user saving was false, then it was becuase of duplicate e-mail at this point
else
{
MessageBox.Show("The e-mail already exists in the database! Please choose another one.");
return false;
}
}
return false;
}
/**
* @desc This method will save or update a room in the ROOMS 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 SaveRoom()
{
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_room assigned to this room,
// if not then this a new room to save
if (this.Id_room == -1)
{
// Create insert query
query = "insert into `gym`.`rooms` (`id_room`, `name`, `size`, `description`) values " +
"(NULL, '" + this.Name + "', '" + this.Size + "', '" + this.Description + "')";
// Launch insert query
int id_room = conn.InsertToDB(query);
// Check saving result
if (id_room != -1)
{
this.Id_room = id_room;
MessageBox.Show("The new room has been added to the databse succesfully!");
return true;
}
else
{
MessageBox.Show("There was a problem adding the new room, please check your data!");
return false;
}
}
// If an id_room already exists for this room instance, then this is an existing room to update
else
{
// Create update query
query = "UPDATE rooms SET name = '" + this.Name + "', size = '" + this.Size + "', description = '" + this.Description + "' " +
" WHERE id_room = '" + this.Id_room + "'";
// Launch update query
int result = conn.DeleteOrUpdate(query);
// Check update reults
if (result > -1)
{
MessageBox.Show("The room data has been updated succesfully!");
return true;
}
else
{
MessageBox.Show("There was a problem updating the room information, please check your data!");
return false;
}
}
}
return false;
}
/**
* @desc This method will save or update a class instance in the CLASS_INSTANCE 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 SaveClassInstance()
{
// Create mysql connection
mySqlConn conn = new mySqlConn();
conn.connect();
// SAVING THE CLASS INSTANCE INTO CLASS_INSTANCE
// Check whether there is a new id_class_instance assigned to this class instance,
// if not then this a new class to save
if (this.Id_class_instance == -1)
{
// Create the save query
string saveClInstanceQuery = "insert into `gym`.`class_instance` (`id_class_instance`, `id_class`, `id_staff`, `date`, `start_time`, `end_time`, `frequency`, `id_room`) values " +
"(NULL, '" + this.ClClass.Id_class + "', '" + this.Id_staff + "', '" + Utils.sGetMysqlDate(this.DateStart) + "', '" + this.StartTime + "', '" + this.EndTime + "', '" + this.Frequency + "', '" + this.ClRoom.Id_room + "');";
// Launch save query
int id_class_instance = conn.InsertToDB(saveClInstanceQuery);
// Check saving result
if (id_class_instance != -1)
{
this.Id_class_instance = id_class_instance;
MessageBox.Show("The class has been saved!");
return true;
}
else
{
MessageBox.Show("There has been an error creating the class instance! Contact with your administrator.");
}
}
// If an id_class_instance already exists for this class instance, then this is an existing class instance to update
else
{
string updateClInstanceQuery = "UPDATE class_instance SET id_staff= '" + this.Id_staff + "', date = '" + Utils.sGetMysqlDate(this.DateStart) + "', start_time = '" + this.StartTime + "', end_time = '" + this.EndTime + "', frequency = '" + this.Frequency + "', id_room = '" + this.ClRoom.Id_room + "' " +
"WHERE id_class_instance = '" + this.Id_class_instance + "'";
// Launch update query
int result = conn.DeleteOrUpdate(updateClInstanceQuery);
// Check update result
if (result > 0)
{
MessageBox.Show("The class booking data has been updated succesfully!");
}
else
{
MessageBox.Show("There was a problem updating the class booking information, please check your data!");
return false;
}
}
// SAVING THE ATTENDANTS INTO CLASS_BOOKINGS
// If there are any attendants save them into the CLASS_BOOKINGS table
if (this.lclAttendants.Count > 0)
{
StringBuilder sbQueryClassBooking = new StringBuilder();
// Create the first half of the insert query containing so far only the fields
sbQueryClassBooking.Append("insert into `gym`.`class_bookings` (`id_class_booking`, `id_member`, `id_class_instance`, `booking_date`) values ");
int i = 0;
// Create a string array storing each second half of the queries, that is the values of each booking
string[] aQueryClassBookingValues = new string[this.lclAttendants.Count];
// Copy all values of each booking into each string in the array
foreach (Member clMember in this.lclAttendants)
{
aQueryClassBookingValues[i] = "(NULL, '" + clMember.Id_member + "', '" + this.Id_class_instance + "', NOW())";
i++;
}
// It is possible to add more than one class booking into the CLASS_BOOKINGS table in the same query at the same time
// so append each set of booking values one after the other at the end of the query, separated by comma
sbQueryClassBooking.Append(string.Join(", ", aQueryClassBookingValues));
sbQueryClassBooking.Append(" ON DUPLICATE KEY UPDATE booking_date = booking_date");
int lastMemberId = conn.InsertToDB(sbQueryClassBooking.ToString());
if (lastMemberId != -1)
{
MessageBox.Show("The attendant has been enrolled!");
}
}
return true;
}
/**
* @desc Removes the class instance from the CLASS_INSTANCE table.
* @params [none] No input parameter.
* @return [bool] Returns true in case of success, false if there was problem deleting the class instance.
*/
public bool RemoveClassInstance()
{
if (this.Id_class_instance != 0)
{
// Create mysql connection
mySqlConn conn = new mySqlConn();
conn.connect();
// Create the delete query
string query = "DELETE FROM class_instance WHERE id_class_instance = '" + this.Id_class_instance + "'";
// 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;
}
}
MessageBox.Show("There is no class id!");
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;
}
}