/**
* @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;
}
}