public void defaultOtTable()
{
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand("select e.EmpId,FirstName,LastName,o.OTHours from employee e LEFT OUTER JOIN ot o ON e.EmpId = o.EmpId where o.Month = @mon;", connDatabase);
q4.Parameters.AddWithValue("@mon", "January");
try
{
//MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = q4;
//DataTable dataset = new DataTable();
datasetOT.Clear();
sda.Fill(datasetOT);
//BindingSource bSource = new BindingSource();
bindingSource1.DataSource = datasetOT;
dataGridView4.DataSource = bindingSource1;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
public void refreshEmpLeaveTable()
{
if (tabSendLeaveRequests.Text == "Send Leave Requests")
{
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand("select RequestedDate,StartDate,EndDate,NoOfDays,Type,Reason,Status from leave_view where EmpId = @empid ;", connDatabase);
q4.Parameters.AddWithValue("@empId", Connection.getUserIdFromConnectionString());
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = q4;
DataTable dataset = new DataTable();
sda.Fill(dataset);
BindingSource bSource = new BindingSource();
bSource.DataSource = dataset;
dataGridView1.DataSource = bSource;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
}
public void getNoOfLeaveDays()
{
try
{
String a = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Annual' ;";
String c = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Casual';";
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommandAnnual = new MySqlCommand(a, MyConn);
MySqlCommand SelectCommandCasual = new MySqlCommand(c, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommandAnnual.ExecuteReader();
if (MyReader.Read())
{
labRemAnnual.Text = (14 - MyReader.GetInt32(0)).ToString();
}
MyConn.Close();
MyReader = null;
MyConn.Open();
MyReader = SelectCommandCasual.ExecuteReader();
if (MyReader.Read())
{
int x;
x = MyReader.GetInt32(0);
labRemCasual.Text = (7 - x).ToString();
}
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
public void refreshHRLeaveTable()
{
if (tabCheckLeaveRequests.Text == "Check Leave Requests")
{
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand("select EmpId,RequestedDate,StartDate,EndDate,NoOfDays,Type,Reason,Status from leave_view where Status = @status;", connDatabase);
q4.Parameters.AddWithValue("@status", "Pending");
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = q4;
DataTable dataset = new DataTable();
sda.Fill(dataset);
BindingSource bSource = new BindingSource();
bSource.DataSource = dataset;
dataGridView3.DataSource = bSource;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
}
private void Rem_Click_1(object sender, EventArgs e)
{
if (dataGridView2.SelectedRows.Count > 0)
{
DataGridViewRow row = this.dataGridView2.SelectedRows[0];
emp = row.Cells["EmpId"].Value.ToString();
}
String q2 = "delete from itp.employee where EmpId ='" + emp + "' ";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand cmdDatabase = new MySqlCommand(q2, connDatabase);
MySqlDataReader myReader;
try
{
connDatabase.Open();
myReader = cmdDatabase.ExecuteReader();
//MessageBox.Show("Record is deleted successfully");
PanMessage.Show(this.MdiParent, "Record Deleted", "Record is deleted successfully");
displayDataGridViewSearch();
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
public void refreshOtTable()
{
//MessageBox.Show("Hello");
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand(@"select e.EmpId,FirstName,LastName,o.OTHours
from employee e
LEFT OUTER JOIN (
select EmpId,OTHours from ot where Month = '" + mon + "' ) o ON o.EmpId =e.EmpId;",
connDatabase);
//q4.Parameters.AddWithValue("@mon", mon);
try
{
// MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = q4;
//DataTable dataset = new DataTable();
datasetOT.Clear();
sda.Fill(datasetOT);
//BindingSource bSource = new BindingSource();
bindingSource1.DataSource = datasetOT;
dataGridView4.DataSource = bindingSource1;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
private void button4_Click(object sender, EventArgs e)
{
int Annual = Int32.Parse(labRemAnnual.Text);
int Casual = Int32.Parse(labRemCasual.Text);
DateTime endDate = dateTimePicker2.Value;
DateTime startDate = dateTimePicker3.Value;
TimeSpan tspan = endDate - startDate;
int differenceInDays = tspan.Days;
if ((this.Type_combo.Text == "Annual") && (differenceInDays > Annual))
{
PanException.Show(this.MdiParent, "Annual leave", "You are exceeding the number of remaining annual days");
return;
}
if ((this.Type_combo.Text == "Casual") && (differenceInDays > Casual))
{
PanException.Show(this.MdiParent, "Casual leave", "You are exceeding the number of remaining casual days");
return;
}
DateTime start = Convert.ToDateTime(dateTimePicker3.Text);
DateTime end = Convert.ToDateTime(dateTimePicker2.Text);
String query = "insert into `leave`(Type,Reason,StartDate,EndDate,RequestedDate,Status,EmpId) values('" + this.Type_combo.Text + "','" + this.Reason.Text + "', @date1 , @date2 , CURDATE(),'Pending', '" + Connection.getUserIdFromConnectionString() + "')";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand cmdDatabase = new MySqlCommand(query, connDatabase);
MySqlParameter param = new MySqlParameter("@date1", MySqlDbType.Date);
param.Value = dateTimePicker3.Value;
MySqlParameter param2 = new MySqlParameter("@date2", MySqlDbType.Date);
param2.Value = dateTimePicker2.Value;
cmdDatabase.Parameters.Add(param);
cmdDatabase.Parameters.Add(param2);
try
{
connDatabase.Open();
cmdDatabase.ExecuteNonQuery();
//MessageBox.Show("Records are saved successfully\n");
PanMessage.Show(this.MdiParent, "Success", "Your leave request has been sent");
refreshEmpLeaveTable();
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
public void Insert()
{
isEmpty();
if (!checkMail(this.Email_txt.Text))
{
//MessageBox.Show("Not a valid Email address ");
PanException.Show(this.MdiParent, "Validation Error", "Not a valid Email address");
return;
}
else if (labNationality.Text == "NIC" && (!checkNIC(this.NIC_txt.Text)))
{
//MessageBox.Show("Not a valid NIC Number ");
PanException.Show(this.MdiParent, "Validation Error", "Not a valid NIC Number");
return;
}
else if (Double.Parse(textBox5.Text) < 10000)
{
//MessageBox.Show("Basic Salary should be above 10000");
PanException.Show(this.MdiParent, "Validation Error", "Basic Salary should be above 10000");
return;
}
/*if (!checkMobile())
* {
* MessageBox.Show("Mobile Number should have 10 digits");
* return;
* }*/
String us = assignUserName();
int val = getNewId();
Double sal = Double.Parse(this.textBox5.Text);
String query = "insert into itp.employee(EmpId,FirstName,LastName,Nationality,Email,MobileNo,NIC,Position,DepId,Gender,DOB,Address,UserName,Password,BasicSalary) values('" + this.Emp_txt.Text + "','" + this.First_txt.Text + "','" + this.Last_txt.Text + "','" + nat + "','" + this.Email_txt.Text + "','" + this.Mob_txt.Text + "','" + this.NIC_txt.Text + "','" + this.Posi_combo.Text + "','" + dep + "','" + gender + "','" + this.dateTimePicker1.Text + "','" + this.Add_txt.Text + "','" + us + "','" + pw + "','" + sal + "'); ";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand cmdDatabase = new MySqlCommand(query, connDatabase);
MySqlCommand createUser = new MySqlCommand("CREATE_USER", connDatabase);
createUser.CommandType = CommandType.StoredProcedure;
createUser.Parameters.AddWithValue("user_id_", this.Emp_txt.Text);
createUser.Parameters.AddWithValue("password_", pw);
createUser.Parameters.AddWithValue("name_", this.First_txt.Text + " " + this.Last_txt.Text);
try
{
connDatabase.Open();
cmdDatabase.ExecuteNonQuery();
createUser.ExecuteNonQuery();
//MessageBox.Show("Records are saved successfully\n" + this.First_txt.Text + this.Last_txt.Text + " 's Username is " + us);
PanMessage.Show(this.MdiParent, "Success", "Records are saved successfully\n" + this.First_txt.Text + this.Last_txt.Text + " 's Username is " + us);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
//public void getMonthComboValues()
//{
// String[] m = {"January","February","March","April","May","June","July","August","September","October","November","December"};
// MySqlConnection MyConn = ConnectionOld.getConnection();
// MySqlCommand SelectCommand = new MySqlCommand("select distinct Month from ot;", MyConn);
// MySqlDataReader MyReader;
// MyConn.Open();
// MyReader = SelectCommand.ExecuteReader();
// while (MyReader.Read())
// {
// String mon = MyReader.GetString("Month");
// for (int i = 0; i < m.Length; i++)
// {
// if (mon != m[0])
// {
// combo_Month.Items.Add(mon);
// }
// }
// }
//}
public void getDetails()
{
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand("select * from itp.employee where EmpId = '" + Connection.getUserIdFromConnectionString() + "' ;", MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
while (MyReader.Read())
{
Emp_txt.Text = MyReader.GetInt32("EmpId").ToString();
First_txt.Text = MyReader.GetString("FirstName");
Last_txt.Text = MyReader.GetString("LastName");
dateTimePicker1.Text = MyReader.GetString("DOB");
if ((MyReader.GetString("Nationality") == "SriLankan"))
{
combo_Nat.Text = "SriLankan";
}
else
{
combo_Nat.Text = "Foreigner";
Nat_txt.Text = MyReader.GetString("Nationality");
}
NIC_txt.Text = MyReader.GetString("NIC");
Add_txt.Text = MyReader.GetString("Address");
Email_txt.Text = MyReader.GetString("Email");
Mob_txt.Text = MyReader.GetString("MobileNo");
Posi_combo.Text = MyReader.GetString("Position");
Dep_txt.Text = MyReader.GetString("DepId");
textBox5.Text = MyReader.GetDouble("BasicSalary").ToString();
String gender = MyReader.GetChar("Gender").ToString();
if (gender == "M")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
}
}
/* public bool checkMobile()
* {
* if (Mob_txt.TextLength < 7 && Mob_txt.TextLength > 15)
* {
* return false;
* }
* return true;
* }
*/
public int getNewId()
{
String s = "select max(EmpId) from itp.employee;";
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand(s, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
while (MyReader.Read())
{
ss = MyReader.GetInt32("max(EmpId)");
}
int n = ss + 1;
return(n);
}
public void setDetails()
{
String q2 = "update itp.employee set FirstName ='" + this.First_txt.Text + "', LastName ='" + this.Last_txt.Text + "',Gender ='" + gender + "', Address ='" + this.Add_txt.Text + "', MobileNo ='" + this.Mob_txt.Text + "', Email ='" + this.Email_txt.Text + "' where EmpId = '" + Connection.getUserIdFromConnectionString() + "' ";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand cmdDatabase = new MySqlCommand(q2, connDatabase);
MySqlDataReader myReader;
try
{
connDatabase.Open();
myReader = cmdDatabase.ExecuteReader();
//MessageBox.Show("Records are edited successfully");
PanMessage.Show(this.MdiParent, "Success", "Records are edited successfully");
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
private void dataGridView3_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (dataGridView3.SelectedRows.Count > 0)
{
DataGridViewRow row = this.dataGridView3.SelectedRows[0];
emp = row.Cells["EmpId"].Value.ToString();
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand("select FirstName,DepId from itp.employee where EmpId = '" + emp + "' ;", MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
while (MyReader.Read())
{
textBox11.Text = MyReader.GetString("FirstName");
textBox12.Text = MyReader.GetString("DepId");
}
}
}
private void button5_Click(object sender, EventArgs e)
{
String q2 = "update itp.leave set Status ='Denied' where EmpId = '" + emp + "' ";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand cmdDatabase = new MySqlCommand(q2, connDatabase);
MySqlDataReader myReader;
try
{
connDatabase.Open();
myReader = cmdDatabase.ExecuteReader();
//MessageBox.Show("You have rejected the request");
PanMessage.Show(this.MdiParent, "Leave Request", "Leave request rejected");
refreshHRLeaveTable();
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand("select * from teemah.employee where UserName = '******' and Password = '******' ;", MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
int count = 0;
while (MyReader.Read())
{
count++;
}
if (count == 1)
{
un = MyReader.GetString("UserName");
ei = MyReader.GetString("EmpId");
//MessageBox.Show("Username and Password is correct");
this.Hide();
Employee em = new Employee();
em.Show();
}
else if (count > 1)
{
MessageBox.Show("Duplicate Username and Password are there ");
}
else
{
MessageBox.Show("Username and Password is incorrect");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void displayDataGridViewSearch()
{
q5 = "select EmpId,FirstName,LastName,Position,DepId,Address,MobileNo,Email from itp.employee ;";
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand(q5, connDatabase);
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = q4;
dataset = new DataTable();
sda.Fill(dataset);
BindingSource bSource = new BindingSource();
bSource.DataSource = dataset;
dataGridView2.DataSource = bSource;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
public void refreshSalaryTable()
{
MySqlConnection connDatabase = ConnectionOld.getConnection();
MySqlCommand q4 = new MySqlCommand("select Month,NetSalary from salary where EmpId = @empid ;", connDatabase);
q4.Parameters.AddWithValue("@empId", Connection.getUserIdFromConnectionString());
try
{
MySqlDataAdapter sal = new MySqlDataAdapter();
sal.SelectCommand = q4;
DataTable dt = new DataTable();
sal.Fill(dt);
BindingSource bSal = new BindingSource();
bSal.DataSource = dt;
metroGrid1.DataSource = bSal;
//sda.Update(dataset);
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
private void Employee_Load(object sender, EventArgs e)
{
Emp_txt.ReadOnly = true;
dateTimePicker3.MinDate = DateTime.Now.AddDays(1);
dateTimePicker2.MinDate = dateTimePicker3.MinDate.AddDays(1);
dateTimePicker2.MaxDate = DateTime.Now.AddMonths(2);
refreshSalaryTable();
combo_Month.SelectedIndex = 0;
radioButton1.Enabled = true;
radioButton2.Enabled = true;
AddOT.TabPages.Remove(tabCheckLeaveRequests);
AddOT.TabPages.Remove(tabAddOT);
//AddOT.TabPages.Remove(tabCalSalary);
MySqlConnection con = ConnectionOld.getConnection();
MySqlCommand sc = new MySqlCommand("select role from user_role_tab where user = @user", con);
sc.Parameters.AddWithValue("@user", Connection.getUserIdFromConnectionString());
try
{
con.Open();
MySqlDataReader read = sc.ExecuteReader();
while (read.Read())
{
string role = read.GetString("role");
switch (role)
{
case "HR Manager": AddNewEmp.Show();
Save.Show();
AddOT.TabPages.Remove(tabSendLeaveRequests);
//AddOT.TabPages.Remove(tabViewSalary);
//this.tabViewSalary.Text = "Calculate Salary";
AddOT.TabPages.Add(tabCheckLeaveRequests);
AddOT.TabPages.Add(tabAddOT);
//AddOT.TabPages.Add(tabCalSalary);
Rem.Show();
//Emp_txt.ReadOnly = false;
First_txt.ReadOnly = false;
Last_txt.ReadOnly = false;
//radioButton1.Enabled = true;
//radioButton2.Enabled = true;
dateTimePicker1.Enabled = true;
Nat_txt.ReadOnly = false;
NIC_txt.ReadOnly = false;
Add_txt.ReadOnly = false;
Mob_txt.ReadOnly = false;
Email_txt.ReadOnly = false;
Posi_combo.Enabled = true;
textBox5.ReadOnly = false;
combo_Nat.Enabled = true;
break;
}
}
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
finally
{
con.Close();
}
/* first2 = Login.un.Substring(0, 2);
* if (tabControl1.SelectedIndex == 0 && first2 != "HR")
* {
* AddNewEmp.Hide();
* Rem.Hide();
* Emp_txt.ReadOnly = true;
* First_txt.ReadOnly = true;
* Last_txt.ReadOnly = true;
* radioButton1.Enabled = false;
* radioButton2.Enabled = false;
* dateTimePicker1.Enabled = false;
* Nat_txt.ReadOnly = true;
* NIC_txt.ReadOnly = true;
* Add_txt.ReadOnly = false;
* Mob_txt.ReadOnly = false;
* Email_txt.ReadOnly = false;
* Posi_combo.Enabled = false;
* }
*
* if (tabControl1.SelectedIndex == 1 && first2 != "HR")
* {
* Calculate.Hide();
* button2.Hide();
* }
*
* if (first2 != "HR")
* {
* tabControl1.TabPages.Remove(tabPage5);
* tabControl1.TabPages.Remove(tabPage2);
* }
* else
* {
* tabControl1.TabPages.Remove(tabPage3);
* this.tabPage2.Text = "Calculate Salary";
* }
*
* if (tabPage4.Text == "Search Employees" && first2 == "HR")
* {
* Rem.Show();
* }*/
}