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;
}
