private string identifyExpiredMeetingIDQuery()
{
Club club = new Club();
Meeting m = new Meeting(session);
expiredMeetingID = m.getExpiredMeetingID();
if (expiredMeetingID.Count.Equals(0))
{
string query = "delete from meeting where meeting_id in (0)";
return(query);
}
else
{
string queryMeeting = "delete from meeting where meeting_id in (";
for (int i = 0; i < expiredMeetingID.Count; i++)
{
if (i.Equals(expiredMeetingID.Count - 1))
{
queryMeeting += expiredMeetingID[i];
}
else
{
queryMeeting += expiredMeetingID[i] + ",";
}
}
queryMeeting += ") and club_id = " + club.getClubIDFromPresident(session);
return(queryMeeting);
}
}
public int getSecureMeetingID()
{
Club club = new Club();
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "select meeting_id from dbo.Meeting where meeting_name = '"
+ getMeetingName()
+ "' and meeting_location = '" + getMeetingLocation()
+ "' and meeting_description = '" + getMeetingDescription()
+ "' and meeting_starttime = '" + getStartTime()
+ "' and meeting_endtime = '" + getEndTime()
+ "' and club_id = " + club.getClubIDFromPresident(session);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
meetingID = Convert.ToInt32(reader["meeting_id"].ToString());
}
reader.Close();
connect.close();
return(meetingID);
}
private void addDataIntoRegister()
{
Club club = new Club();
Member member = new Member();
// get student id for register table
member.setName(studentNameTextbox.Text);
studentID = member.getMemberID(member.getName());
// get club id for register table
string clubName = club.getClubNameDisplay(session);
int clubID = club.getClubIDFromPresident(session);
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "insert into dbo.register values(" + studentID + "," + clubID + ",CURRENT_TIMESTAMP, " + club.getClubFeesFromDb(clubID) + ", '0')";
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
}
private void displayAllField()
{
Meeting m = new Meeting(session);
Club club = new Club();
query = "select student_name, student_intakecode, student_handphone, student_gender from student s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id inner join meeting as m on c.club_id = m.club_id where m.club_id = " +
club.getClubIDFromPresident(session) + "and m.meeting_name = '" + meetingCombobox.Text + "'";
displayData(query);
}
private void searchAttendanceOnDisplay()
{
Club club = new Club();
query = "select distinct student_name, student_intakecode, student_email, student_handphone, student_gender from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id inner join meeting as m on c.club_id = m.club_id right join attendance as a on a.student_id = s.student_id where m.club_id = " +
club.getClubIDFromPresident(session) + "and m.meeting_name = '" + meetingNameComboBox.Text + "'";
displayAttendance(query);
}
private void searchButton_Click(object sender, EventArgs e)
{
Club club = new Club();
query = "select student_name, student_handphone, student_email, student_gender, student_intakecode from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id where c.club_id = " +
club.getClubIDFromPresident(session) + " and s.student_name like '%" + searchTextbox.Text + "%'";
displayData(query);
clearField();
}
private void updateButton_Click(object sender, EventArgs e)
{
validateEmptyField();
if (insertStudentData)
{
Member member = new Member(studentNameTextbox.Text, Convert.ToInt64(studenthpTextbox.Text), studentEmailTextbox.Text,
studentGenderCombobox.Text, intakeCodeTextbox.Text);
// get club id for register table
Club club = new Club();
int clubID = club.getClubIDFromPresident(session);
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "update s set s.student_name = '" + member.getName() +
"', s.student_handphone = " + member.getHP() +
", s.student_email = '" + member.getEmail() +
"', s.student_gender = '" + member.getGender() +
"', s.student_intakecode = '" + member.getIntakeCode() +
"' from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id where r.student_id = " +
studentID + " and r.club_id = " + clubID;
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
displayAllField(); // display all the data
clearField(); // clear text field
// return success message
messageText.ForeColor = System.Drawing.Color.Green;
messageText.Text = "Member information UPDATE successfully ! ";
}
else
{
// warning message
messageText.ForeColor = System.Drawing.Color.Red;
messageText.Text = "UPDATE action is INVALID \n because some field is empty or wrong ! ";
}
addButton.Visible = true;
}
private void updateButton_Click(object sender, EventArgs e)
{
validateEmptyField();
if (insertStudentData)
{
// calculate the fees even though without pressing the button
calculateFees();
// determine the paid status if the fees left is zero
Fee f = new Fee();
f.setFeesLeft(leftFees);
int paidStatus = f.calculatePaidStatus();
// get club id for register table
Club club = new Club();
int clubID = club.getClubIDFromPresident(session);
// get student id for register table
Member member = new Member();
member.setName(studNameLabel.Text);
int studentID = member.getMemberID(member.getName());
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "update r set r.member_fees = " + leftFees + ", member_paid = " + paidStatus + " from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id where r.student_id = " +
studentID + " and r.club_id = " + clubID;
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
displayFeeSummary(); // display all the fees summary
clearField(); // clear text field */
MessageBox.Show("The club fee had UPDATED successfully! ");
}
else
{
MessageBox.Show("UPDATE action is INVALID \nbecause some field is empty or wrong !");
}
}
private void addButton_Click(object sender, EventArgs e)
{
validateEmptyField();
if (insertMeetingData)
{
Meeting m = new Meeting(session);
m.setName(nameTextbox.Text);
m.setLocation(locationTextbox.Text);
m.setStartTime(datePicker.Value, startTimePicker.Value);
m.setEndTime(datePicker.Value, endTimePicker.Value);
m.setDescription(descriptionTextbox.Text);
m.calculateDuration();
Club club = new Club();
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "insert into dbo.meeting values (" + club.getClubIDFromPresident(session) +
",'" + m.getMeetingName() + "','" + m.getMeetingLocation() + "','" + m.getMeetingDescription() +
"','" + m.getStartTime() + "','" + m.getEndTime() + "'," + m.getDuration() + ")";
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
// success message
messageText.ForeColor = System.Drawing.Color.Green;
messageText.Text = "Create meeting successful. ";
displayAllField();
clearField();
}
else
{
MessageBox.Show("Your action is INVALID because some field is empty or wrong");
// warning message
messageText.ForeColor = System.Drawing.Color.Red;
messageText.Text = "INSERT action is INVALID \n because some field is empty or wrong ! ";
}
}
public List <int> getExpiredMeetingID()
{
Club club = new Club();
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "select distinct meeting_id from dbo.meeting where meeting_endtime < CURRENT_TIMESTAMP and club_id = " +
club.getClubIDFromPresident(session);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
expiredMeetingID.Add(Convert.ToInt32(reader["meeting_id"].ToString()));
}
reader.Close();
connect.close();
return(expiredMeetingID);
}
private void updateButton_Click(object sender, EventArgs e)
{
validateEmptyField();
if (insertMeetingData)
{
Meeting m = new Meeting(session);
m.setName(nameTextbox.Text);
m.setLocation(locationTextbox.Text);
m.setStartTime(datePicker.Value, startTimePicker.Value);
m.setEndTime(datePicker.Value, endTimePicker.Value);
m.setDescription(descriptionTextbox.Text);
m.calculateDuration();
Club club = new Club();
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "update dbo.meeting set meeting_name = '" + m.getMeetingName()
+ "', meeting_location = '" + m.getMeetingLocation()
+ "', meeting_description = '" + m.getMeetingDescription()
+ "', meeting_starttime = '" + m.getStartTime()
+ "', meeting_endtime = '" + m.getEndTime()
+ "', meeting_duration = " + m.getDuration() +
"where meeting_id = " + meetingID + " and club_id = " + club.getClubIDFromPresident(session);
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
// success message
messageText.ForeColor = System.Drawing.Color.Green;
messageText.Text = "Update meeting details successful. ";
displayAllField();
clearField();
}
else
{
MessageBox.Show("Your UPDATE action is INVALID because some field is empty or wrong");
// warning message
messageText.ForeColor = System.Drawing.Color.Red;
messageText.Text = "UPDATE action is INVALID \n because some field is empty or wrong ! ";
}
addButton.Visible = true;
}
public void loadMeetingCombobox()
{
Club club = new Club();
SqlConn sqlConn = new SqlConn();
SqlDataAdapter sda = new SqlDataAdapter("select meeting_name from dbo.meeting where club_id = " + club.getClubIDFromPresident(session), sqlConn.sqlConnection);
DataTable dt = new DataTable();
sda.Fill(dt);
meetingCombobox.Items.Clear();
meetingCombobox1.Items.Clear();
foreach (DataRow item in dt.Rows)
{
meetingCombobox.Items.Add(item[0].ToString());
meetingCombobox1.Items.Add(item[0].ToString());
}
}
private void displayAllField()
{
Club club = new Club();
query = "select student_name, student_handphone, student_email, student_gender, student_intakecode from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id where c.club_id = " + club.getClubIDFromPresident(session);
displayData(query);
}
private void displayFeeSummary()
{
query = "select student_name, member_fees, member_paid, register_date from student as s inner join register as r on s.student_id = r.student_id inner join club as c on r.club_id = c.club_id where r.club_id = " +
club.getClubIDFromPresident(session);
displayData(query);
}
private void deleteButton_Click(object sender, EventArgs e)
{
validateEmptyField();
if (insertMeetingData)
{
Meeting m = new Meeting(session);
m.setName(nameTextbox.Text);
m.setLocation(locationTextbox.Text);
m.setStartTime(datePicker.Value, startTimePicker.Value);
m.setEndTime(datePicker.Value, endTimePicker.Value);
m.setDescription(descriptionTextbox.Text);
m.calculateDuration();
Club club = new Club();
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "delete dbo.meeting where meeting_id = " + meetingID + " and club_id = " + club.getClubIDFromPresident(session);
SqlDataReader reader = command.ExecuteReader();
reader.Close();
connect.close();
// success message
messageText.ForeColor = System.Drawing.Color.Green;
messageText.Text = "Delete meeting details successful. ";
displayAllField();
clearField();
}
else
{
MessageBox.Show("Your Delete action is INVALID because you did not specifiy a meeting details");
// warning message
messageText.ForeColor = System.Drawing.Color.Red;
messageText.Text = "Your Delete action is INVALID because you did not specifiy a meeting details";
}
}
private void loadDataIntoDetails()
{
Meeting m = new Meeting(session);
Club club = new Club();
m.setName(meetingNameComboBox.Text);
try
{
SqlConn connect = new SqlConn();
connect.open();
SqlCommand command = new SqlCommand();
command.Connection = connect.sqlConnection;
command.CommandText = "select * from dbo.Meeting where meeting_name = '" + m.getMeetingName() + "' and club_id = " + club.getClubIDFromPresident(session);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
m.setName(reader.GetString(2));
m.setLocation(reader.GetString(3));
m.setDescription(reader.GetString(4));
m.setStartDate(reader.GetDateTime(5));
m.setEndDate(reader.GetDateTime(6));
// automate value on textfield
nameLabel.Text = m.getMeetingName();
locationLabel.Text = m.getMeetingLocation();
startTimeLabel.Text = m.getStartDate().ToString();
endTimeLabel.Text = m.getEndDate().ToString();
descriptionLabel.Text = m.getMeetingDescription().ToString();
durationLabel.Text = m.getReportDurationText().ToString();
// close connection
connect.close();
reader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("error " + ex);
}
}
private void searchButton_Click(object sender, EventArgs e)
{
Club club = new Club();
query = "select meeting_name, meeting_location, meeting_description, meeting_starttime, meeting_endtime, meeting_duration from meeting where club_id = " + club.getClubIDFromPresident(session) +
" and meeting_name like '%" + searchTextbox.Text + "%'";
displayData(query);
clearField();
}
private void displayAllField()
{
Club club = new Club();
query = "select meeting_name, meeting_location, meeting_description, meeting_starttime, meeting_endtime, meeting_duration from meeting where club_id = " + club.getClubIDFromPresident(session) +
" and meeting_starttime > CURRENT_TIMESTAMP";
displayData(query);
}