public void setupCourses()
{
xGrid.DataSource = null;
xGrid.Rows.Clear();
xGrid.Columns.Clear();
xGrid.Refresh();
MySqlConnection con = MySQLConnection.openCon();
string query = "SELECT course_name, course_id FROM course WHERE course_id IN (SELECT course_id from student_course WHERE student_id=" + user.user_id + " AND course_name='" + this.Text + "')";
if (user.usertype == 2)
{
query = "SELECT course_name, course_id FROM course WHERE course_id IN (SELECT course_id from faculty_course WHERE faculty_id=" + user.user_id + " AND course_name='" + this.Text + "')";
}
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
cid = Int32.Parse(dtb1.Rows[0][1].ToString());
if (dtb1.Rows.Count >= 1)
{
string cname = dtb1.Rows[0][0].ToString();
query = "SELECT id as ID, file_name as 'File Name', date_uploaded as 'Date Uploaded', file_title as Title, description as Description FROM course_data WHERE course_id = " + cid.ToString();
MySqlCommand newcmd = new MySqlCommand(query, con);
dataReader = newcmd.ExecuteReader();
DataTable dtb2 = new DataTable();
dtb2.Load(dataReader);
if (dtb2.Rows.Count >= 1)
{
this.dtb = dtb2;
xGrid.DataSource = dtb;
//xGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
xGrid.Columns[0].Visible = false;
xGrid.Columns[1].Width = 100;
xGrid.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
xGrid.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
xGrid.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
}
else
{
xGrid.Columns.Add("A", "Oops!");
xGrid.Rows.Add("No data found for this course");
xGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
con.Close();
}
}
public void setupCourses()
{
MySqlConnection con = MySQLConnection.openCon();
string query = "SELECT * FROM course INNER JOIN student_course WHERE student_id = " + user.user_id + " AND student_course.course_id = course.course_id";
if (user.usertype == 2)
{
query = "SELECT * FROM course INNER JOIN faculty_course WHERE faculty_id = " + user.user_id + " AND faculty_course.course_id = course.course_id";
}
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
con.Close();
if (dtb1.Rows.Count > 0)
{
for (int i = 0; i < dtb1.Rows.Count; i++)
{
Button b = new Button();
b.Text = dtb1.Rows[i][1].ToString();
b.Width = 188;
b.Height = 100;
var margin = b.Margin;
margin.Left = 30;
b.Tag = "Course Button";
b.Margin = margin;
b.BackColor = ColorTranslator.FromHtml("#303030");
b.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
b.Font = new Font("Verdana", 10);
b.ForeColor = ColorTranslator.FromHtml("#ffffff");
b.Click += new EventHandler(b_Click);
flowLayoutPanel1.Controls.Add(b);
}
flowLayoutPanel1.AutoScroll = true;
}
else
{
Label lbl = new Label();
lbl.Text = "No courses enrolled/added yet!";
lbl.Width = 488;
lbl.Height = 100;
var margin = lbl.Margin;
margin.Left = 30;
lbl.Margin = margin;
lbl.Font = new Font("Century Gothic", 18);
lbl.ForeColor = ColorTranslator.FromHtml("#303030");
flowLayoutPanel1.Controls.Add(lbl);
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
string title = txtTitle.Text.ToString();
string des = rtDesc.Text.ToString();
string description = des.Replace("'", "\"");
string query = "INSERT INTO course_data (course_id, faculty_id, file_name, date_uploaded, file_title, description) VALUES (" + course_id + ", " + user.user_id + ", '" + fileName.ToString() + "', NOW(), '" + title + "', '" + description + "')";
if (!chkAddFile.Checked)
{
query = "INSERT INTO course_data (course_id, faculty_id, file_name, date_uploaded, file_title, description) VALUES (" + course_id + ", " + user.user_id + ", null, NOW(), '" + title + "', '" + description + "')";
}
MySqlConnection con = MySQLConnection.openCon();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
if (chkAddFile.Checked)
{
string sourceFile = txtFileName.Text;
string targetPath = appPath;
string destFile = System.IO.Path.Combine(appPath, fileName);
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
System.IO.File.Copy(sourceFile, destFile, true);
}
MessageBox.Show("Data added.");
this.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public void setUser(User user)
{
xGrid.DataSource = null;
xGrid.Rows.Clear();
xGrid.Columns.Clear();
xGrid.Refresh();
this.user = user;
txtTitle.Text = "Students of " + user.dept_name + " - Semester " + user.semester + ":";
xGrid.RowHeadersVisible = false;
MySqlConnection con = MySQLConnection.openCon();
string query = "select student_id AS 'ID', CONCAT(first_name, ' ', last_name) AS 'Full Name', email as 'Contact' from student where dept_id = (SELECT dept_id from student where student_id=" + user.user_id + ") AND semester = (SELECT semester from student where student_id = " + user.user_id + ") AND student_id != " + user.user_id;
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
if (dtb1.Rows.Count >= 1)
{
xGrid.DataSource = dtb1;
xGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
xGrid.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
xGrid.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
else
{
xGrid.DataSource = null;
xGrid.Rows.Clear();
xGrid.Columns.Clear();
xGrid.Refresh();
xGrid.Columns.Add("A", "Oops!");
xGrid.Rows.Add("No other students enrolled in " + user.dept_name + " - Semester " + user.semester + "!");
xGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
con.Close();
}
private void btnDelete_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Do you want to delete the selected data?", "Delete Data?", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
foreach (DataGridViewCell oneCell in xGrid.SelectedCells)
{
if (oneCell.Selected)
{
MySqlConnection con = MySQLConnection.openCon();
string data_id = xGrid.CurrentRow.Cells["ID"].Value.ToString();
string query = "DELETE FROM course_data WHERE id = " + data_id.ToString();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
xGrid.Rows.RemoveAt(oneCell.RowIndex);
}
}
setupCourses();
}
}
private void btnUpdateSem_Click(object sender, EventArgs e)
{
string query = "UPDATE student SET semester = " + comboSemester.SelectedItem.ToString() + " WHERE student_id = " + user.user_id;
try
{
MySqlConnection con = MySQLConnection.openCon();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();;
this.updatesemester = true;
if (this.comboSemester.SelectedItem != null)
{
this.semester = Int32.Parse(comboSemester.SelectedItem.ToString());
}
MessageBox.Show("Updated semester!");
}
catch (MySqlException ex2)
{
MessageBox.Show(ex2.Message);
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
if (txtEmail.Text == "" || txtFname.Text == "" || txtLname.Text == "" || txtPassword.Text == "" || txtEmail.Text == "email" || txtFname.Text == "first name" || txtLname.Text == "last name" || txtPassword.Text == "password" || comboDept.SelectedItem.ToString() == "" || comboSemester.SelectedItem.ToString() == "")
{
MessageBox.Show("Please enter all the fields");
}
else
{
string email = txtEmail.Text.Trim();
string fname = txtFname.Text.Trim();
string lname = txtLname.Text.Trim();
string pw = txtPassword.Text;
//int dept = comboDept.SelectedIndex;
int dept = comboDept.SelectedIndex + 1;
int semester = Int32.Parse(comboSemester.SelectedItem.ToString());
//MessageBox.Show("email: " + email + ", fname: " + fname + ", lname: " + lname + ", pw: " + pw + "deptid: " + dept + ", semester: " + semester);
if (!IsValidEmail(email))
{
MessageBox.Show("Enter a valid email address!");
}
else
{
string myHash = BCrypt.HashPassword(pw, BCrypt.GenerateSalt());
MySqlConnection con = MySQLConnection.openCon();
string query = "INSERT INTO student (first_name, last_name, email, password, dept_id, photo, semester) VALUES('" + fname + "', '" + lname + "', '" + email + "', '" + myHash + "', " + dept + ", 'default.jpg', " + semester + ")";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Registered!");
formLogin frmLogin = new formLogin();
this.Hide();
frmLogin.Show();
}
}
}
public void setUser(User user)
{
this.user = user;
comboDept.Items.Clear();
comboDept2.Items.Clear();
dataGridView1.RowHeadersVisible = false;
MySqlConnection con = MySQLConnection.openCon();
string query = "SELECT * FROM department";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
for (int i = 0; i < dtb1.Rows.Count; i++)
{
comboDept.Items.Add(dtb1.Rows[i]["department_name"]);
comboDept2.Items.Add(dtb1.Rows[i]["department_name"]);
}
}
public void setUser(User user)
{
this.user = user;
txtName.Text = user.fname + " " + user.lname;
txtDept.Text = user.dept_name;
txtContact.Text = user.email;
txtSemester.Text = user.semester.ToString();
MySqlConnection con = MySQLConnection.openCon();
string query = "select course_code as 'Course Code', course_name as 'Course Name' from course where course_id in (select course_id from student_course where student_id = " + user.user_id + ")";
if (user.usertype == 2)
{
label5.Hide();
txtSemester.Hide();
label7.Location = label5.Location;
txtContact.Location = txtSemester.Location;
label1.Text = "Teaching the following courses:";
query = "select course_code as 'Course Code', course_name as 'Course Name' from course where course_id in (select course_id from faculty_course where faculty_id = " + user.user_id + ")";
}
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
dataGridView1.RowHeadersVisible = false;
dataGridView1.DataSource = dtb1;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
private void btnLogin_Click(object sender, EventArgs e)
{
MySqlConnection con = MySQLConnection.openCon();
//MessageBox.Show("Connection Open ! ");
string email = txtEmail.Text.Trim();
string password = txtPassword.Text;
string query = "SELECT * from student WHERE email='" + email + "'";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dtb1 = new DataTable();
dtb1.Load(dataReader);
//cmd.ExecuteNonQuery();
//new SqlDataAdapter(query, con).Fill(dtb1);
if (dtb1.Rows.Count == 1)
{
bool doesmatch = BCrypt.CheckPassword(password, dtb1.Rows[0][4].ToString());
if (doesmatch)
{
//MessageBox.Show("user_id: " + dtb1.Rows[0][0] + "\nName: " + dtb1.Rows[0][1] + " " + dtb1.Rows[0][2] + "\nEmail: " + dtb1.Rows[0][3] + "\nDept_Id: " + dtb1.Rows[0][5] + "\nSemester: " + dtb1.Rows[0][7]);
user1.setValues((int)dtb1.Rows[0][0], 1, (int)dtb1.Rows[0][5], (int)dtb1.Rows[0][7], dtb1.Rows[0][1].ToString(), dtb1.Rows[0][2].ToString(), dtb1.Rows[0][3].ToString());
formMain frm = new formMain(this);
this.Hide();
frm.Show();
}
else
{
MessageBox.Show("Unable to login!");
}
}
else
{
query = "SELECT * from faculty WHERE email='" + email + "'";
cmd = new MySqlCommand(query, con);
dataReader = cmd.ExecuteReader();
DataTable dtb2 = new DataTable();
dtb2.Load(dataReader);
if (dtb2.Rows.Count == 1)
{
bool doesmatch = BCrypt.CheckPassword(password, dtb2.Rows[0][4].ToString());
if (doesmatch)
{
user1.setValues((int)dtb2.Rows[0][0], 2, (int)dtb2.Rows[0][5], -1, dtb2.Rows[0][1].ToString(), dtb2.Rows[0][2].ToString(), dtb2.Rows[0][3].ToString());
formMain frm = new formMain(this);
this.Hide();
frm.Show();
}
else
{
MessageBox.Show("Unable to login!");
}
}
else
{
MessageBox.Show("User not found");
}
}
MySQLConnection.closeCon(con);
}
private void btnUpdatePw_Click(object sender, EventArgs e)
{
if (txtOldPw.Text == "old password" && txtNewPw.Text != "new password")
{
MessageBox.Show("Please enter old password");
}
else if (txtNewPw.Text == "new password" && txtOldPw.Text != "old password")
{
MessageBox.Show("Please enter new password");
}
else
{
try
{
MySqlConnection con = MySQLConnection.openCon();
string query = "SELECT * FROM student WHERE student_id = " + user.user_id;
if (user.usertype == 2)
{
query = "SELECT * FROM faculty WHERE faculty_id = " + user.user_id;
}
MySqlCommand cmd = new MySqlCommand(query, con);
DataTable dtb1 = new DataTable();
MySqlDataReader dataReader = cmd.ExecuteReader();
dtb1.Load(dataReader);
//cmd.ExecuteNonQuery();
//new SqlDataAdapter(query, con).Fill(dtb1);
if (dtb1.Rows.Count == 1)
{
bool doesmatch = BCrypt.CheckPassword(txtOldPw.Text, dtb1.Rows[0][4].ToString());
if (doesmatch)
{
string myHash = BCrypt.HashPassword(txtNewPw.Text, BCrypt.GenerateSalt());
query = "UPDATE student SET password = '******' WHERE student_id = " + user.user_id;
if (user.usertype == 2)
{
query = "UPDATE faculty SET password = '******' WHERE faculty_id = " + user.user_id;
}
try
{
cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Updated password!");
}
catch (MySqlException ex2)
{
MessageBox.Show(ex2.Message);
}
}
else
{
MessageBox.Show("Old password incorrect!");
}
}
}
catch (MySqlException ex)
{
MessageBox.Show("MySQLException: " + ex.Message);
}
}
}