/// <summary>
/// The function is written to add an employee to the database
/// </summary>
/// <param name="employeeDetails">an object of type EmployeeDetails that contains the personal data of the employee</param>
/// <param name="companyData">an object of the type CompanyData that contains data regarding regarding the company</param>
/// <param name="department">an object of type Department that conatins data regarding the employee's department in the company</param>
/// <param name="payrollDetails">an object of type PayrollDetails the contains data regaarding the payroll of the employee</param>
public static int AddAnEmployee(EmployeeDetails employeeDetails, CompanyData companyData, Department department, PayrollDetails payrollDetails)
{
int count = 0;
SqlConnection sqlConnection = ConnectionSetUp();
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
string query1 = "insert into payroll_details values(" + payrollDetails.SalaryID + "," + payrollDetails.BasicPay + "," + payrollDetails.Deduction + "," + payrollDetails.Taxable + "," + payrollDetails.IncomeTax + "," + payrollDetails.NetPay + ");";
string query2 = "insert into employee_details values('" + employeeDetails.EmployeeID + "','" + employeeDetails.Name + "','" + employeeDetails.StartDate + "','" + employeeDetails.Gender + "','" + employeeDetails.Phone + "','" + employeeDetails.SalaryID + "','" + employeeDetails.Address + "'," + employeeDetails.IsActive + ");";
string query3 = "insert into company values(" + companyData.DepartmentID + ",'" + companyData.DepartmentName + "');";
string query4 = "insert into department values(" + department.DepartmentID + "," + department.EmployeeID + ");";
SqlCommand cmd1 = new SqlCommand(query1, sqlConnection, sqlTransaction);
SqlCommand cmd2 = new SqlCommand(query2, sqlConnection, sqlTransaction);
SqlCommand cmd3 = new SqlCommand(query3, sqlConnection, sqlTransaction);
SqlCommand cmd4 = new SqlCommand(query4, sqlConnection, sqlTransaction);
count += cmd1.ExecuteNonQuery();
count += cmd2.ExecuteNonQuery();
count += cmd3.ExecuteNonQuery();
count += cmd4.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (Exception e)
{
System.Console.WriteLine(e.Message);
sqlTransaction.Rollback();
}
sqlConnection.Close();
return(count);
}
/// <summary>
/// The function is written to take user input and perform the corresponding action
/// </summary>
public static void ImplementEmployeePayroll()
{
Console.WriteLine("Enter 1 to see all the data in the database" +
"\n2 to Update the basic pay of an employee" +
"\n3 to see the employees joine between the data range" +
"\n4 to see data grouped by gender" +
"\n5 to add an employee" +
"\n6 to remove an employee from the database" +
"\n7 to exit.");
int option = Convert.ToInt32(Console.ReadLine());
switch (option)
{
case 1:
HandleDatabase.RetrieveFromDataBase();
break;
case 2:
Console.WriteLine("Enter the name and the updated basic pay of the employee");
string name = Console.ReadLine();
int newBasicPay = Convert.ToInt32(Console.ReadLine());
HandleDatabase.UpdateDataBase(name, newBasicPay);
break;
case 3:
Console.WriteLine("Employee joined between the date range 01/01/2000 and 01/01/2000");
HandleDatabase.JoinedInDateRange(Convert.ToDateTime("01/01/2000"), Convert.ToDateTime("01/01/2000"));
break;
case 4:
Console.WriteLine("Grouping By Gender");
HandleDatabase.GroupByGender();
break;
case 5:
EmployeeDetails employeeDetails = new EmployeeDetails()
{
EmployeeID = 20,
Name = "New Name 20",
StartDate = Convert.ToDateTime("06/04/2020"),
Gender = "F",
Phone = "7889564512",
SalaryID = 120,
Address = "New Address",
IsActive = 0
};
CompanyData companyData = new CompanyData()
{
DepartmentID = 515,
DepartmentName = "New Department"
};
Department department = new Department()
{
DepartmentID = 515,
EmployeeID = 20
};
int basicPay = 7900;
PayrollDetails payrollDetails = new PayrollDetails()
{
SalaryID = 120,
BasicPay = basicPay,
Deduction = (int)(basicPay * .2),
IncomeTax = (int)((basicPay - (int)(basicPay * .2)) * .1),
NetPay = (int)(basicPay - (int)((basicPay - (int)(basicPay * .2)) * .1)),
Taxable = basicPay - (int)(basicPay * .2)
};
HandleDatabase.AddAnEmployee(employeeDetails, companyData, department, payrollDetails);
break;
case 6:
Console.WriteLine("Enter the employee id of the employee to be remmoved");
int empid = Convert.ToInt32(Console.ReadLine());
HandleDatabase.RemoveFromDataBase(empid);
break;
}
}