private string getDownloadFile()
{
string downloadFile = "";
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "SELECT app_path FROM applications WHERE application_id = @application_id";
command.Parameters.AddWithValue("@application_id", appId);
using (MySqlDataReader dr = command.ExecuteReader())
{
try
{
while (dr.Read())
{
downloadFile = dr[0].ToString();
}
}
catch (Exception ex)
{ System.Console.WriteLine("Not found..."); }
}
dbConnection.CloseConnection();
command.Dispose();
}
return(downloadFile);
}
public void RefreshUserList()
{
usersListView.Items.Clear();
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
if (search)
{
command.CommandText = "SELECT member_id, f_name, l_name, position FROM member_info WHERE f_name LIKE @fname AND l_name LIKE @lname AND position LIKE @position;";
command.Parameters.Add("@fname", MySqlDbType.VarChar, 200).Value = "%" + txtFirstName.Text + "%";
command.Parameters.Add("@lname", MySqlDbType.VarChar, 200).Value = "%" + txtLastName.Text + "%";
command.Parameters.Add("@position", MySqlDbType.VarChar, 200).Value = "%" + positionLevel.Text + "%";
}
else
{
command.CommandText = "SELECT member_id, f_name, l_name, position FROM member_info";
}
using (MySqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ListViewItem item = new ListViewItem();
item.Text = dr[0].ToString();
item.SubItems.Add(dr[1].ToString());
item.SubItems.Add(dr[2].ToString());
item.SubItems.Add(dr[3].ToString());
usersListView.Items.Add(item);
}
}
dbConnection.CloseConnection();
command.Dispose();
}
}
public void RefreshApplicationList()
{
applicationList.Items.Clear();
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
if (securityStatus == 4)
{
command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id AND passOn = 'yes'";
}
else if (securityStatus == 3)
{
command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id AND passOn is NULL";
}
else
{
command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id";
}
command.Parameters.AddWithValue("@job_id", jobId);
using (MySqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ListViewItem item = new ListViewItem();
item.Text = dr[0].ToString();
item.SubItems.Add(dr[1].ToString());
item.SubItems.Add(dr[2].ToString());
item.SubItems.Add(dr[3].ToString());
item.SubItems.Add(dr[4].ToString());
item.SubItems.Add(dr[5].ToString());
applicationList.Items.Add(item);
}
}
dbConnection.CloseConnection();
command.Dispose();
foreach (ListViewItem li in applicationList.Items)
{
if (li.SubItems[5].Text == "Yes")
{
li.BackColor = Color.LightGreen;
}
}
foreach (ListViewItem li in applicationList.Items)
{
if ((Int32.Parse(li.SubItems[1].Text)) == sentApp)
{
li.BackColor = Color.LightBlue;
}
}
}
}
public void RefreshJobList()
{
jobList.Items.Clear();
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "SELECT job_id, job_name, job_status FROM dropboxes";
using (MySqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ListViewItem item = new ListViewItem();
item.Text = dr[0].ToString();
item.SubItems.Add(dr[1].ToString());
item.SubItems.Add(dr[2].ToString());
jobList.Items.Add(item);
}
}
dbConnection.CloseConnection();
command.Dispose();
foreach (ListViewItem li in jobList.Items)
{
if (li.SubItems[2].Text == "Approved")
{
li.BackColor = Color.LightGreen;
}
}
}
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
// TODO: Create a new Connection and test before showing mainForm.
DBConnection dbConnection = new DBConnection("159.203.38.0", "ears_db", "root", "12345");
if (dbConnection.OpenConnection())
{
dbConnection.CloseConnection();
SignInForm signInForm = new SignInForm(dbConnection);
signInForm.Show();
Application.Run();
}
else
{
dbConnection.CloseConnection();
MessageBox.Show(null, "Error connecting to the database. Please try again.", "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnReset_Click(object sender, EventArgs e)
{
if (txtNewPassword.TextLength < 1)
{
MessageBox.Show(this, "Please enter a new password.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtConfirmPassword.TextLength < 1)
{
MessageBox.Show(this, "Please confirm the new password.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!txtNewPassword.Text.Equals(txtConfirmPassword.Text))
{
MessageBox.Show(this, "The paasswords entered do not match.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "UPDATE user_login SET password = @password WHERE member_id=@id";
command.Parameters.AddWithValue("@password", txtConfirmPassword.Text);
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
dbConnection.CloseConnection();
command.Dispose();
MessageBox.Show(this, "Password successfully updated.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
private void btnCreate_OnClick(object sender, EventArgs e)
{
try
{
string name = nameBox.Text;
string phone = phoneBox.Text;
string email = emailBox.Text;
if (name == null || phone == null || email == null || filePath == null)
{
throw new Exception();
}
string fileFolder = "";
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "SELECT job_path FROM dropboxes WHERE job_id = @job_id";
command.Parameters.AddWithValue("@job_id", jobId);
using (MySqlDataReader dr = command.ExecuteReader())
{
try
{
while (dr.Read())
{
fileFolder = dr[0].ToString();
}
}
catch (Exception ex)
{ System.Console.WriteLine("Not found..."); }
}
dbConnection.CloseConnection();
command.Dispose();
}
string newApplication = fileFolder + "/" + fileName;
WebRequest request = WebRequest.Create(newApplication);
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential("ftpuser", "pickleparty");
byte[] bytes = System.IO.File.ReadAllBytes(@filePath);
request.ContentLength = bytes.Length;
using (Stream request_stream = request.GetRequestStream())
{
request_stream.Write(bytes, 0, bytes.Length);
request_stream.Close();
}
if (dbConnection.OpenConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "INSERT INTO applications (job_id, name, phone, email, app_path, approved) VALUES (@job_id, @name, @phone, @email, @app_path, @approved)";
command.Parameters.AddWithValue("@job_id", jobId);
Console.WriteLine("JOB ID" + jobId);
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@phone", phone);
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@app_path", (fileFolder + "/" + fileName));
command.Parameters.AddWithValue("@approved", "No");
command.ExecuteNonQuery();
dbConnection.CloseConnection();
command.Dispose();
}
MessageBox.Show("Application successfully created!", "EARS System Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception x)
{
Console.WriteLine("All fields must be complete...");
Console.WriteLine(x);
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
if (txtUsername.TextLength > 0)
{
if (txtPassword.TextLength > 0)
{
if (dbConnection.OpenConnection())
{
int memberId = 0;
String username = "";
String password = "";
int securityStatus = 0;
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "SELECT member_id, username, password, security_status FROM user_login WHERE username=@username";
command.Parameters.AddWithValue("@username", this.txtUsername.Text);
using (MySqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
memberId = Int32.Parse(dr[0].ToString());
username = dr[1].ToString();
password = dr[2].ToString();
securityStatus = Int32.Parse(dr[3].ToString());
}
}
dbConnection.CloseConnection();
command.Dispose();
if (username.Equals(txtUsername.Text) && password.Equals(txtPassword.Text))
{
switch (securityStatus)
{
case 1: // System administrator
SystemAdminForm systemAdminForm = new SystemAdminForm(dbConnection);
systemAdminForm.Show();
signIn = true;
this.Close();
break;
case 2: // Secretary
case 3: // member
case 4: // chair
MemberForm memberForm = new MemberForm(dbConnection, securityStatus, memberId);
memberForm.Show();
signIn = true;
this.Close();
break;
}
}
else
{
MessageBox.Show(this, "Invalid username or password.", "Sign In", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show(this, "Please enter a valid password.", "Invalid Password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Please enter a valid username.", "Invalid Username", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnFinish_Click(object sender, EventArgs e)
{
if (txtFirstName.TextLength < 1 || txtLastName.TextLength < 1 || txtUsername.TextLength < 1 || txtPassword.TextLength < 1)
{
MessageBox.Show(this, "You must fill in all fields.", "Add User", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int securityStatus = 0;
switch (securityComboBox.Text)
{
case "Administrator":
securityStatus = 1;
break;
case "Secretary":
securityStatus = 2;
break;
case "Committee Member":
securityStatus = 3;
break;
case "Chairperson":
securityStatus = 4;
break;
}
if (dbConnection.OpenConnection())
{
int id = 0;
MySqlCommand command = new MySqlCommand();
command.Connection = dbConnection.getConnection();
command.CommandText = "INSERT INTO user_login (username, password, security_status) VALUES (@username, @password, @securityStatus)";
command.Parameters.AddWithValue("@username", txtUsername.Text);
command.Parameters.AddWithValue("@password", txtPassword.Text);
command.Parameters.AddWithValue("@securityStatus", securityStatus);
command.ExecuteNonQuery();
command.CommandText = "SELECT member_id FROM user_login WHERE username=@username";
using (MySqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
id = Int32.Parse(dr[0].ToString());
}
}
command.CommandText = "INSERT INTO member_info (member_id, f_name, l_name, position) VALUES (@id, @firstName, @lastName, @position)";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@firstName", txtFirstName.Text);
command.Parameters.AddWithValue("@lastName", txtLastName.Text);
command.Parameters.AddWithValue("@position", securityComboBox.Text);
command.ExecuteNonQuery();
dbConnection.CloseConnection();
command.Dispose();
MessageBox.Show(this, "User successfully added.", "User Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
