public SalaryDetails GetMonthlySalaryByEmpId(string empid, string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string salaryQuery = "select * from monthlySalary where emp_id = " + empid + " and month = '" + year + "/" + month + "/"+numberOfDays+" 00:00:00.000'"; SqlCommand cmd = new SqlCommand(salaryQuery); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "monthlySalary"); List<SalaryDetails> salaryArtifactList = new List<SalaryDetails>(); SalaryDetails salary = new SalaryDetails(); AttendanceArtifact selectedMonth = new AttendanceArtifact(); foreach (DataRow dr in ds.Tables["monthlySalary"].Rows) { salary.Id = int.Parse(empid); salary.MonthlySalary = float.Parse(dr["monthlySalary"].ToString()); salary.Allowance = float.Parse(dr["allowance"].ToString()); salary.Insurance = float.Parse(dr["insurance"].ToString()); salary.NetSalary = float.Parse(dr["netSalary"].ToString()); salaryArtifactList.Add(salary); } return salary; }
public bool EditEmployee(EmployeeArtifact obj) { string query = "update employee set lname ='" + obj.LastName + "', middlename='" + obj.MiddleName + "' ,designation='" + obj.Designation + "',address='" + obj.Address + "',contactdetails = '" + obj.ContactDetails + "',active = '" + obj.Active + "', deleted= '" + obj.Deleted + "', dateofbirth = '" + obj.DateOfBirth + "',qualification = '"+obj.Qualification+"' where id = " + obj.Id; DbConnectionManager dbConnectionManager = new DbConnectionManager(); SqlConnection connection = dbConnectionManager.connectToDb(); SqlCommand command = new SqlCommand(query); command.Connection = connection; int result = command.ExecuteNonQuery(); dbConnectionManager.diconnectFromDb(connection); if (result > 0) { return true; } else { return false; } }
private void change_btn_Click(object sender, EventArgs e) { if (newpassword_txt.Text == confirmpassword_txt.Text) { checklogin login = new checklogin(); bool isSuccessful = login.CheckLoginCredential(UserUtils.USERNAME.ToString(), oldpassword_txt.Text); if (isSuccessful) { string query = "update login set password ='******' where username = '******' "; DbConnectionManager dbConnectionManager= new DbConnectionManager (); SqlConnection connection=dbConnectionManager.connectToDb(); SqlCommand command = new SqlCommand(query); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { MessageBox.Show("Password Successfully Changed"); } else { MessageBox.Show("Unable to change password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); dbConnectionManager.diconnectFromDb(connection); } } else { MessageBox.Show("Password do not match", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
private void AddAttendanceDetails(string empid) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query1 = "select * from attendance where attendance_date = '" + dateTimePicker1.Value.Date + "'"; SqlCommand cmd1 = new SqlCommand(query1); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "attendance"); if (ds1.Tables["attendance"].Rows.Count != 0) { foreach (DataRow dr in ds1.Tables["attendance"].Rows) { if (empid == (dr["employee_id"].ToString())) { Notice.Text = "Attendance Already Marked"; } else { if (attendanceMarked(empid, conn) == true) { Notice.Text = "Attendance Marked Successfully"; } else { Notice.Text = "Error! Try Again!"; } } } } else { if (attendanceMarked(empid, conn) == true) { Notice.Text = "Attendance Marked Successfully"; } else { Notice.Text = "Error! Try Again!"; } } connect.diconnectFromDb(conn); }
public string GetAttendanceCountByEmpID(string empid, string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' and present = 'True'"; SqlCommand cmd1 = new SqlCommand(attendanceQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "attendance"); return(ds1.Tables["attendance"].Rows.Count.ToString()); }
private void delete_button_Click(object sender, EventArgs e) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query = "delete from employee where id = " + empid_txtbox.Text; SqlCommand cmd = new SqlCommand(query); cmd.Connection = conn; int result = cmd.ExecuteNonQuery(); if (result > 0) { MessageBox.Show("Successfully Deleted"); } else { MessageBox.Show("Error"); } }
private void add_button_Click(object sender, EventArgs e) { bool workstate; if (active.Checked == true) { workstate = true; } else { workstate = false; } DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query = "insert into employee(fname,lname,middlename,designation,address,contactdetails,active,deleted,dateofbirth,qualification) values ('" + fname_txtbox.Text + "','" + lname_txtbox.Text + "','" + mname_txtbox.Text + "','" + designation_txtbox.Text + "','" + address_txtbox.Text + "','" + contact_txtbox.Text + "','" + workstate + "','False','" + dob_pick.Value.Date + "','" + qualification_txtbox.Text + "')"; SqlCommand command = new SqlCommand(query); command.Connection = conn; int result = command.ExecuteNonQuery(); if (result > 0) { string selectid = "select max(id) as id from employee"; command.CommandText = selectid; command.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = command; DataSet ds = new DataSet(); da.Fill(ds, "employee_id"); DataRow dr = ds.Tables["employee_id"].Rows[0]; string employee_id = dr["id"].ToString(); string salary = "insert into salary(employee_id,basic_salary,allowance, insurance) values(" + employee_id + ",0,0,0)"; SqlCommand command1 = new SqlCommand(salary); command1.Connection = conn; command1.ExecuteNonQuery(); MessageBox.Show("New Employee Added"); connect.diconnectFromDb(conn); this.Close(); } else { MessageBox.Show("Failed adding New Employee"); } }
public List <AttendanceArtifact> GetAttendanceCount(string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string noOfDays = numberOfDays; string employeeQuery = "select * from employee "; SqlCommand cmd = new SqlCommand(employeeQuery); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "employee"); List <AttendanceArtifact> attendanceArtifactList = new List <AttendanceArtifact>(); foreach (DataRow dr in ds.Tables["employee"].Rows) { string empid = (dr["id"].ToString()); string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + noOfDays + " 00:00:00.000' and present = 'True'"; SqlCommand cmd1 = new SqlCommand(attendanceQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "attendance"); AttendanceArtifact attendance = new AttendanceArtifact(); attendance.Id = int.Parse(empid); attendance.FirstName = dr["fname"].ToString(); attendance.MiddleName = dr["middlename"].ToString(); attendance.LastName = dr["lname"].ToString(); attendance.Designation = dr["designation"].ToString(); attendance.ContactDetails = dr["contactdetails"].ToString(); attendance.NumberOfPresentDays = ds1.Tables["attendance"].Rows.Count.ToString(); attendanceArtifactList.Add(attendance); connect.diconnectFromDb(conn); } return(attendanceArtifactList); }
private void delete_button_Click(object sender, EventArgs e) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query = "delete from employee where id = " + empid_txtbox.Text; SqlCommand cmd = new SqlCommand(query); cmd.Connection = conn; int result = cmd.ExecuteNonQuery(); if (result > 0) { MessageBox.Show("Successfully Deleted"); } else { MessageBox.Show("Error"); } }
private void add_button_Click(object sender, EventArgs e) { bool workstate; if (active.Checked == true) { workstate = true; } else { workstate = false; } DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query = "insert into employee(fname,lname,middlename,designation,address,contactdetails,active,deleted,dateofbirth,qualification) values ('" + fname_txtbox.Text+"','"+ lname_txtbox.Text + "','" + mname_txtbox.Text +"','"+ designation_txtbox.Text +"','" + address_txtbox.Text+"','"+ contact_txtbox.Text + "','"+workstate+"','False','" + dob_pick.Value.Date +"','" + qualification_txtbox.Text + "')"; SqlCommand command = new SqlCommand(query); command.Connection = conn; int result = command.ExecuteNonQuery(); if (result > 0) { string selectid = "select max(id) as id from employee"; command.CommandText = selectid; command.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = command; DataSet ds = new DataSet(); da.Fill(ds, "employee_id"); DataRow dr = ds.Tables["employee_id"].Rows[0]; string employee_id = dr["id"].ToString(); string salary = "insert into salary(employee_id,basic_salary,allowance, insurance) values(" + employee_id + ",0,0,0)"; SqlCommand command1 = new SqlCommand(salary); command1.Connection = conn; command1.ExecuteNonQuery(); MessageBox.Show("New Employee Added"); connect.diconnectFromDb(conn); this.Close(); } else { MessageBox.Show("Failed adding New Employee"); } }
public List<AttendanceArtifact> GetAttendanceCount(string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string noOfDays = numberOfDays; string employeeQuery = "select * from employee "; SqlCommand cmd = new SqlCommand(employeeQuery); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "employee"); List<AttendanceArtifact> attendanceArtifactList = new List<AttendanceArtifact>(); foreach (DataRow dr in ds.Tables["employee"].Rows) { string empid = (dr["id"].ToString()); string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + noOfDays + " 00:00:00.000' and present = 'True'"; SqlCommand cmd1 = new SqlCommand(attendanceQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "attendance"); AttendanceArtifact attendance = new AttendanceArtifact(); attendance.Id = int.Parse(empid); attendance.FirstName = dr["fname"].ToString(); attendance.MiddleName = dr["middlename"].ToString(); attendance.LastName = dr["lname"].ToString(); attendance.Designation = dr["designation"].ToString(); attendance.ContactDetails = dr["contactdetails"].ToString(); attendance.NumberOfPresentDays = ds1.Tables["attendance"].Rows.Count.ToString(); attendanceArtifactList.Add(attendance); connect.diconnectFromDb(conn); } return attendanceArtifactList; }
public bool UpdateSalaryDetails(SalaryDetails obj) { DbConnectionManager dbConnectionManager = new DbConnectionManager(); SqlConnection connection = dbConnectionManager.connectToDb(); string query = "update salary set basic_salary =" + obj.BasicSalary + " ,allowance =" + obj.Allowance + " ,insurance =" + obj.Insurance + " where employee_id =" + obj.Id; SqlCommand command = new SqlCommand(query); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { return(true); } else { return(false); } }
public List <EmployeeArtifact> GetAllEmployee() { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string employeeQuery = "select * from employee "; SqlCommand cmd1 = new SqlCommand(employeeQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "employee"); List <EmployeeArtifact> empArtifactList = new List <EmployeeArtifact>(); foreach (DataRow dr in ds1.Tables["employee"].Rows) { EmployeeArtifact temp = new EmployeeArtifact(); temp.DateOfBirth = dr["dateofbirth"].ToString(); temp.FirstName = dr["fname"].ToString(); temp.Address = dr["address"].ToString(); temp.Id = int.Parse(dr["id"].ToString()); temp.LastName = dr["lname"].ToString(); temp.MiddleName = dr["middlename"].ToString(); temp.Designation = dr["designation"].ToString(); temp.Qualification = dr["qualification"].ToString(); temp.ContactDetails = dr["contactdetails"].ToString(); temp.Active = bool.Parse(dr["active"].ToString()); temp.Deleted = bool.Parse(dr["deleted"].ToString()); empArtifactList.Add(temp); } return(empArtifactList); }
public bool CheckLoginCredential(string username, string password) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string query = "select * from login " + "where username='******' and password='******'"; SqlCommand cmd = new SqlCommand(query); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "login"); if (ds.Tables["Login"].Rows.Count > 0) { connect.diconnectFromDb(conn); return true; } else { connect.diconnectFromDb(conn); return false; } }
public bool EditEmployee(EmployeeArtifact obj) { string query = "update employee set lname ='" + obj.LastName + "', middlename='" + obj.MiddleName + "' ,designation='" + obj.Designation + "',address='" + obj.Address + "',contactdetails = '" + obj.ContactDetails + "',active = '" + obj.Active + "', deleted= '" + obj.Deleted + "', dateofbirth = '" + obj.DateOfBirth + "',qualification = '" + obj.Qualification + "' where id = " + obj.Id; DbConnectionManager dbConnectionManager = new DbConnectionManager(); SqlConnection connection = dbConnectionManager.connectToDb(); SqlCommand command = new SqlCommand(query); command.Connection = connection; int result = command.ExecuteNonQuery(); dbConnectionManager.diconnectFromDb(connection); if (result > 0) { return(true); } else { return(false); } }
public SalaryDetails GetSalaryCredentialsByEmpId(string empId) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string salaryQuery = "select * from salary where employee_id = " + empId; SqlCommand cmd = new SqlCommand(salaryQuery); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "salary"); List<SalaryDetails> salaryArtifactList = new List<SalaryDetails>(); SalaryDetails salary = new SalaryDetails(); foreach (DataRow dr in ds.Tables["salary"].Rows) { salary.Id = int.Parse((dr["employee_id"].ToString())); salary.BasicSalary = float.Parse(dr["basic_salary"].ToString()); salary.Allowance = float.Parse(dr["allowance"].ToString()); salary.Insurance = float.Parse(dr["insurance"].ToString()); salaryArtifactList.Add(salary); } connect.diconnectFromDb(conn); return salary; }
public bool UpdateSalaryDetails(SalaryDetails obj) { DbConnectionManager dbConnectionManager = new DbConnectionManager(); SqlConnection connection = dbConnectionManager.connectToDb(); string query = "update salary set basic_salary =" + obj.BasicSalary + " ,allowance =" + obj.Allowance + " ,insurance =" + obj.Insurance + " where employee_id =" + obj.Id; SqlCommand command = new SqlCommand(query); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { return true; } else { return false; } }
public List<EmployeeArtifact> GetAllEmployee() { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string employeeQuery = "select * from employee "; SqlCommand cmd1 = new SqlCommand(employeeQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "employee"); List<EmployeeArtifact> empArtifactList = new List<EmployeeArtifact>(); foreach (DataRow dr in ds1.Tables["employee"].Rows) { EmployeeArtifact temp = new EmployeeArtifact(); temp.DateOfBirth = dr["dateofbirth"].ToString(); temp.FirstName = dr["fname"].ToString(); temp.Address = dr["address"].ToString(); temp.Id = int.Parse(dr["id"].ToString()); temp.LastName = dr["lname"].ToString(); temp.MiddleName = dr["middlename"].ToString(); temp.Designation = dr["designation"].ToString(); temp.Qualification = dr["qualification"].ToString(); temp.ContactDetails = dr["contactdetails"].ToString(); temp.Active = bool.Parse(dr["active"].ToString()); temp.Deleted = bool.Parse(dr["deleted"].ToString()); empArtifactList.Add(temp); } return empArtifactList; }
public string GetAttendanceCountByEmpID(string empid, string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' and present = 'True'"; SqlCommand cmd1 = new SqlCommand(attendanceQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "attendance"); return ds1.Tables["attendance"].Rows.Count.ToString(); }
public List<SalaryDetails> GetMonthlySalaryDetails(string month, string year, string numberOfDays) { DbConnectionManager connect = new DbConnectionManager(); SqlConnection conn = connect.connectToDb(); string employeeQuery = "select * from employee "; SqlCommand cmd = new SqlCommand(employeeQuery); cmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, "employee"); List<SalaryDetails> monthlySalaryDetails = new List<SalaryDetails>(); foreach (DataRow dr in ds.Tables["employee"].Rows) { string empid = (dr["id"].ToString()); SalaryDetails salary = new SalaryDetails(); AttendanceArtifact selectedMonth = new AttendanceArtifact(); SalaryDetails tempObj = salary.GetSalaryCredentialsByEmpId(empid); salary.FirstName = dr["fname"].ToString(); salary.MiddleName = dr["middlename"].ToString(); salary.LastName = dr["lname"].ToString(); salary.Designation = dr["designation"].ToString(); salary.Id = int.Parse(empid); salary.BasicSalary = tempObj.BasicSalary; salary.NoOfPresentDays = int.Parse(selectedMonth.GetAttendanceCountByEmpID(empid, month, year, numberOfDays)); salary.MonthlySalary = (salary.BasicSalary * salary.NoOfPresentDays); salary.Allowance = (salary.MonthlySalary * (tempObj.Allowance / 100)); salary.Insurance = (salary.MonthlySalary * (tempObj.Insurance/100)); salary.NetSalary = (salary.MonthlySalary + salary.Allowance - salary.Insurance); monthlySalaryDetails.Add(salary); string redundantCheckQuery = "select * from monthlySalary where emp_id = " + int.Parse(empid) + " and month = '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000'"; SqlCommand cmd1 = new SqlCommand(redundantCheckQuery); cmd1.Connection = conn; SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = cmd1; DataSet ds1 = new DataSet(); da1.Fill(ds1, "monthlySalary"); if (ds1.Tables["monthlySalary"].Rows.Count == 0) { string salaryQuery = "insert into monthlySalary(emp_id,month,monthlySalary,allowance,insurance,netSalary) values( " + int.Parse(empid) + ",'" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' ," +salary.MonthlySalary+","+salary.Allowance+","+salary.Insurance+","+ salary.NetSalary + ")"; SqlCommand command = new SqlCommand(salaryQuery); command.Connection = conn; command.ExecuteNonQuery(); } } connect.diconnectFromDb(conn); return monthlySalaryDetails; }