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 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 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);
}
private void DisplayEmployeeDetails(string employeename)
{
EmployeeArtifact selectedEmployee = (EmployeeArtifact)empname_combobox.SelectedItem;
SalaryDetails salaryParameter = new SalaryDetails();
SalaryDetails tempObj = salaryParameter.GetSalaryCredentialsByEmpId(selectedEmployee.Id.ToString());
emp_idtxtbox.Text = selectedEmployee.Id.ToString();
emplname_txtbox.Text = selectedEmployee.LastName;
designation_txtbox.Text = selectedEmployee.Designation;
basicSalary_txt.Text = tempObj.BasicSalary.ToString();
allowance_txt.Text = tempObj.Allowance.ToString();
insurance_txt.Text = tempObj.Insurance.ToString();
}
private void DisplayEmployeeDetails(string employeename)
{
EmployeeArtifact selectedEmployee = (EmployeeArtifact)empname_combobox.SelectedItem;
SalaryDetails salaryParameter = new SalaryDetails();
SalaryDetails tempObj = salaryParameter.GetSalaryCredentialsByEmpId(selectedEmployee.Id.ToString());
emp_idtxtbox.Text = selectedEmployee.Id.ToString();
emplname_txtbox.Text = selectedEmployee.LastName;
designation_txtbox.Text = selectedEmployee.Designation;
basicSalary_txt.Text = tempObj.BasicSalary.ToString();
allowance_txt.Text = tempObj.Allowance.ToString();
insurance_txt.Text = tempObj.Insurance.ToString();
}
private void show_button_Click(object sender, EventArgs e)
{
SalaryDetails salary = new SalaryDetails();
Month selectedmonth = (Month)selectMonth_combobox.SelectedItem;
List<SalaryDetails> list = new List<SalaryDetails>();
if (DateTime.Parse(selectedmonth.Id + "/" + selectedmonth.NumberOfDays + "/" + selectYear_combobox.SelectedItem.ToString()) <= System.DateTime.Today.Date)
{
list = salary.GetMonthlySalaryDetails(selectedmonth.Id.ToString(), selectYear_combobox.SelectedItem.ToString(), selectedmonth.NumberOfDays.ToString());
dataGridView1.DataSource = list;
}
else {
MessageBox.Show("The selected date exceeds today's date", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Update_button_Click(object sender, EventArgs e)
{
SalaryDetails artifactObj = new SalaryDetails();
artifactObj.Id = int.Parse(emp_idtxtbox.Text);
artifactObj.BasicSalary = float.Parse(basicSalary_txt.Text);
artifactObj.Insurance = float.Parse(insurance_txt.Text);
artifactObj.Allowance = float.Parse(allowance_txt.Text);
if (artifactObj.UpdateSalaryDetails(artifactObj))
{
MessageBox.Show("Successfully updated");
}
else
{
MessageBox.Show("Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Update_button_Click(object sender, EventArgs e)
{
SalaryDetails artifactObj = new SalaryDetails();
artifactObj.Id = int.Parse(emp_idtxtbox.Text);
artifactObj.BasicSalary = float.Parse(basicSalary_txt.Text);
artifactObj.Insurance = float.Parse(insurance_txt.Text);
artifactObj.Allowance = float.Parse(allowance_txt.Text);
if (artifactObj.UpdateSalaryDetails(artifactObj))
{
MessageBox.Show("Successfully updated");
}
else
{
MessageBox.Show("Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void show_button_Click(object sender, EventArgs e)
{
SalaryDetails salary = new SalaryDetails();
Month selectedmonth = (Month)selectMonth_combobox.SelectedItem;
List <SalaryDetails> list = new List <SalaryDetails>();
if (DateTime.Parse(selectedmonth.Id + "/" + selectedmonth.NumberOfDays + "/" + selectYear_combobox.SelectedItem.ToString()) <= System.DateTime.Today.Date)
{
list = salary.GetMonthlySalaryDetails(selectedmonth.Id.ToString(), selectYear_combobox.SelectedItem.ToString(), selectedmonth.NumberOfDays.ToString());
dataGridView1.DataSource = list;
}
else
{
MessageBox.Show("The selected date exceeds today's date", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DisplayEmployeeDetails()
{
EmployeeArtifact selectedEmployee = (EmployeeArtifact)fname_combobox.SelectedItem;
lname_txtbox.Text = selectedEmployee.LastName;
Month selectedMonth = (Month)selectMonth_combobox.SelectedItem;
SalaryDetails salaryParameter = new SalaryDetails();
SalaryDetails tempObj = salaryParameter.GetMonthlySalaryByEmpId(selectedEmployee.Id.ToString(), selectedMonth.Id.ToString(), selectYear_combobox.SelectedItem.ToString(), selectedMonth.NumberOfDays.ToString());
emp_idtxtbox.Text = selectedEmployee.Id.ToString();
designation_txtbox.Text = selectedEmployee.Designation;
monthlySalary_txtbox.Text = tempObj.MonthlySalary.ToString();
netSalary_txtbox.Text = tempObj.NetSalary.ToString();
allowance_txt.Text = tempObj.Allowance.ToString();
insurance_txt.Text = tempObj.Insurance.ToString();
}
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 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 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 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 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;
}
