/**
* @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 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 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 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 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 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 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 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 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 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;
}
/**
* @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 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 Executes when the Search button is clicked
* It creates a new list of members 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_member ID, member_number Nr, sid SID, CONCAT(lastName, ', ', firstName) 'Member Name', DATE_FORMAT(birthdate,\"%d-%m-%Y\") DOB, email 'EMail', type Type, IF((is_active= 0), 'INACTIVE','ACTIVE') Status FROM members 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 + "%'";
if (txt_sid.Text != "")
query += " AND sid LIKE '%" + txt_sid.Text + "%'";
if (cmb_type.SelectedIndex != -1)
{
if (cmb_type.SelectedIndex == 1)
{
query += " AND type LIKE '%Student Full Time%'";
query += " OR type LIKE '%Student Part Time%'";
}
else if (cmb_type.SelectedIndex != 0)
query += " AND type LIKE '%" + cmb_type.SelectedItem.ToString() + "%'";
}
string sDate = Utils.sGetMysqlDate(txt_dob.Text);
if (sDate != "0000-00-00")
query += " AND birthdate = '" + sDate + "'";
query += " ORDER BY ID";
// Launch query and load result into source
itemsSource.DataSource = conn.dtGetTableForDataGrid(query);
// Assign source to grid
dg_members.DataSource = itemsSource;
dg_members.AllowUserToAddRows = false;
dg_members.ReadOnly = true;
// Display amount of people currently on list
label_numberOfPeople.Text = dg_members.RowCount.ToString();
}
/**
* @desc This method refreshes the member list
* @params [none] No input parameter.
* @return [none] No directly returned data.
*/
public void vLoadMemberList()
{
string query = "";
// Create mysql connection
mySqlConn conn = new mySqlConn();
conn.connect();
// Create source for grid
BindingSource itemsSource = new BindingSource();
// If this is not for viewing current attendants for a class instance
if (this.viewAttendants == false)
// Create query for displaying all members
query = "SELECT id_member ID, member_number Nr, sid SID, CONCAT(lastName, ', ', firstName) 'Member Name', DATE_FORMAT(birthdate,\"%d-%m-%Y\") DOB, email 'EMail', type Type, IF((is_active= 0), 'INACTIVE','ACTIVE') Status FROM members ORDER BY ID ";
// If this is for viewing current attendants for a class instance
else
// Create query for displaying only members who attend this class instance
query = "SELECT m.id_member ID, m.member_number Nr, sid SID, CONCAT(m.lastName, ', ', m.firstName) 'Member Name', DATE_FORMAT(m.birthdate,\"%d-%m-%Y\") DOB, email 'EMail', type Type, IF((is_active= 0), 'INACTIVE','ACTIVE') Status FROM members m, class_bookings cb WHERE m.id_member = cb.id_member AND cb.id_class_instance = '" + this.clClassInstance.Id_class_instance + "' ORDER BY ID";
// Launch query and load result into source
itemsSource.DataSource = conn.dtGetTableForDataGrid(query);
// Assign source to grid
dg_members.DataSource = itemsSource;
dg_members.AllowUserToAddRows = false;
dg_members.ReadOnly = true;
// Display current number of people on list
label_numberOfPeople.Text = dg_members.RowCount.ToString();
}
