/// <summary>
/// Adds employee information to all tables in ER Diagram
/// </summary>
/// <param name="employeeName"></param>
/// <param name="gender"></param>
/// <param name="phoneNumber"></param>
/// <param name="address"></param>
/// <param name="startDate"></param>
/// <param name="basicPay"></param>
/// <param name="departmentId"></param>
/// <param name="department"></param>
public bool AddEmployeeToDtabase(EmployeeModel employee)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlTransaction transaction = null;
int employeeId = -1;
try
{
using (connection)
{
connection.Open();
string addEmployeeQuery = @"insert into employee values ('" +
employee.EmployeeName + "','" + employee.Gender + "','" +
employee.PhoneNumber + "','" + employee.Address + "', 1); " +
"Select @@identity";
transaction = connection.BeginTransaction();
SqlCommand addEmployeeCommand = new SqlCommand(addEmployeeQuery, connection, transaction);
try
{
employeeId = Convert.ToInt32(addEmployeeCommand.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
double deduction = 0.2 * Convert.ToDouble(employee.BasicPay);
double taxablePay = Convert.ToDouble(employee.BasicPay) - deduction;
double incomeTax = taxablePay * 0.1;
double netPay = taxablePay - incomeTax;
string addPayrollQuery = @"insert into payroll values ('" +
employeeId + "','" + employee.StartDate.ToString("yyyy-MM-dd") + "','" +
employee.BasicPay + "','" + Convert.ToDecimal(deduction) + "','" +
Convert.ToDecimal(taxablePay) + "','" +
Convert.ToDecimal(incomeTax) + "','" + Convert.ToDecimal(netPay) + "');";
SqlCommand addPayrollCommand = new SqlCommand(addPayrollQuery, connection, transaction);
try
{
var payrollAdded = addPayrollCommand.ExecuteNonQuery();
}
catch (Exception)
{
transaction.Rollback();
}
string addDepartmentQuery = @"insert into EmployeeDepartment values ('" +
employee.DepartmentId + "','" + employee.Department + "','" +
employeeId + "'); ";
SqlCommand addDepartmentCommand = new SqlCommand(addDepartmentQuery, connection, transaction);
try
{
var departmentAdded = addDepartmentCommand.ExecuteNonQuery();
}
catch (Exception)
{
transaction.Rollback();
}
transaction.Commit();
}
return(true);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
finally
{
connection.Close();
}
return(false);
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to the employee payroll service !!!!");
EmployeeRepository repo = new EmployeeRepository();
EmployeeModel emp = new EmployeeModel();
// repo.GetEmployees();
/*emp.Name = "Liam";
* emp.Basic_Pay = 90000;
* emp.Start_Date = "22/2/2019";
* emp.Gender = "Male";
* emp.Mobile_number = "9945670983";
* emp.Address = "England";
* emp.Department = "Music";
* emp.Deductions = 6000;
* emp.Taxable_Pay = 3000;
* emp.Income_Tax = 2900;
* emp.Net_Pay = 87000;
* // repo.AddEmployee(emp);
* emp.Name = "Liam";
* emp.Address = "UK";
* emp.Department = "Production";*/
// repo.UpdateEmployee(emp);
//repo.GetEmployeesInDateRange();
//repo.FindingSumOfSalaryByGender();
// repo.FindingAverageOfSalaryByGender();
//repo.FindingMinimumOfSalaryByGender();
//repo.FindingMaximumOfSalaryByGender();
//repo.CountContactsByGender();
// emp.Name = "Liam";
// repo.DeleteTheEmployee(emp);
/*emp.Start_Date = new DateTime(2020,04,14);
* emp.Basic_Pay = 90000;
* emp.Deductions = 6000;
* emp.Taxable_Pay = 3000;
* emp.Income_Tax = 2900;
* emp.Net_Pay = 87000;
* emp.Employee_Id = 4;*/
//repo.AddingPayRollDetails(emp);
emp.Name = "Pansy";
emp.Gender = 'F';
emp.Mobile_number = "98456831234";
emp.Address = "London";
emp.Basic_Pay = 700000;
emp.Department_Id = 5;
emp.Start_Date = new DateTime(2020, 09, 14);
// repo.AddEmployeeDetailsToMultipleTables(emp);
// repo.AddingDepartment(emp);
//repo.AddingEmployeeDetails(emp);
/*emp.Employee_Id = 4;
* emp.Department_Id = 6;*/
//repo.AddingToEmployeeDepartment(emp);
// repo.GettingEmployeeDetails();
bool i = true;
while (i)
{
Console.WriteLine("\n");
Console.WriteLine("1.Sum Of Basic Salary By Gender");
Console.WriteLine("2.Average Of Basic Salary By Gender");
Console.WriteLine("3.Minimum Of Basic Salary By Gender");
Console.WriteLine("4.Maximum Of Basic Salary By Gender");
Console.WriteLine("5.Count Of Basic Salary By Gender");
Console.WriteLine("6.Exit");
try
{
int choice = Convert.ToInt32(Console.ReadLine());
switch (choice)
{
case 1:
repo.FindingSumOfSalaryByGender();
break;
case 2:
repo.FindingAverageOfSalaryByGender();
break;
case 3:
repo.FindingMinimumOfSalaryByGender();
break;
case 4:
repo.FindingMaximumOfSalaryByGender();
break;
case 5:
repo.CountContactsByGender();
break;
case 6:
i = false;
break;
default:
Console.WriteLine("Choose valid option");
break;
}
}
catch (System.FormatException formatException)
{
Console.WriteLine(formatException);
}
}
}
/// <summary>
/// Ability for Employee Payroll Service to retrieve the Employee Payroll from the Database
/// </summary>
public void GetAllEmployee()
{
try
{
EmployeeModel employeeModel = new EmployeeModel();
using (this.sqlconnection)
{
string query = @"SELECT EmpId,EmpName,Salary,Start_Date,
Gender,Phone_Number,Employee_Address,Department,
Basic_Pay,Deductions,Taxable_Pay,Income_Tax,Net_Pay
FROM Employee_Payroll;";
SqlCommand sqlCommand = new SqlCommand(query, this.sqlconnection);
this.sqlconnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
employeeModel.EmpId = sqlDataReader.GetInt32(0);
employeeModel.EmpName = sqlDataReader.GetString(1);
employeeModel.Salary = (double)sqlDataReader.GetDecimal(2);
employeeModel.Start_Date = sqlDataReader.GetDateTime(3);
employeeModel.Gender = Convert.ToChar(sqlDataReader.GetString(4));
employeeModel.Phone_Number = sqlDataReader.GetString(5);
employeeModel.Employee_Address = sqlDataReader.GetString(6);
employeeModel.Department = sqlDataReader.GetString(7);
employeeModel.Basic_Pay = (double)sqlDataReader.GetDecimal(8);
employeeModel.Deductions = (double)sqlDataReader.GetDecimal(9);
employeeModel.Taxable_Pay = (double)sqlDataReader.GetDecimal(10);
employeeModel.Income_Tax = (double)sqlDataReader.GetDecimal(11);
employeeModel.Net_Pay = (double)sqlDataReader.GetDecimal(12);
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}",
employeeModel.EmpId, employeeModel.EmpName, employeeModel.Salary,
employeeModel.Start_Date, employeeModel.Gender, employeeModel.Phone_Number,
employeeModel.Employee_Address, employeeModel.Department, employeeModel.Basic_Pay,
employeeModel.Deductions, employeeModel.Taxable_Pay, employeeModel.Income_Tax,
employeeModel.Net_Pay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No data found");
}
sqlDataReader.Close();
this.sqlconnection.Close();
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
finally
{
this.sqlconnection.Close();
}
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to Employee Payroll Service!");
EmployeeRepo employeeRepo = new EmployeeRepo();
EmployeeModel employeeModel = new EmployeeModel();
SalaryUpdateModel updateModel = new SalaryUpdateModel();
///Get All Employee present in Employee_Payroll table
employeeRepo.GetAllEmployee();
///Update Employee Salary
employeeModel.SalaryId = 1;
employeeModel.SalaryMonth = "Jan";
employeeModel.Salary = 500000.00;
employeeModel.EmpId = 2;
employeeRepo.UpdateEmployeeSalary(updateModel);
///Get All Employee in a particular data range
employeeRepo.GetAllEmployeeInADataRange();
///Get data by Gender
employeeRepo.GetDataByGroupByGender();
///Add Employee Details
employeeModel.EmpName = "Appu";
employeeModel.Salary = 500000.00;
employeeModel.Start_Date = Convert.ToDateTime("12/05/2020");
employeeModel.Gender = 'F';
employeeModel.Phone_Number = "9877553212";
employeeModel.Employee_Address = "Kharadi";
employeeModel.Department = "Tetsing";
employeeModel.Basic_Pay = 8765.00;
employeeModel.Deductions = 9876.00;
employeeModel.Taxable_Pay = 97765.00;
employeeModel.Income_Tax = 7654.00;
employeeModel.Net_Pay = 6543.00;
employeeModel.EmpId = 6;
employeeModel.DeptId = 7;
employeeModel.DeptName = "Development";
employeeModel.DeptLocation = "Pune";
employeeModel.SalaryMonth = "Jan";
///Add Employee in Employee_Payroll table
employeeRepo.AddEmployee(employeeModel);
///Remove particular employee entry using EmpId
employeeRepo.RemoveEmployee();
///Get Employee Details present in Employee table
employeeRepo.GetAllEmployeeDetails();
///Get Department details from Department table
employeeRepo.GetAllDepartment();
///Get Salary of Employee from Salary table
employeeRepo.GetEmployeeSalary();
///Get Data by Gender using joins
employeeRepo.GetDataByGroupByGenderER();
Console.WriteLine("Employee Payroll using Threads");
///retrieve url
string[] words = CreateWordArray(@"http://www.gutenberg.org/files/54700/54700-0.txt");
#region ParallelTasks
Parallel.Invoke
(() =>
{
Console.WriteLine("Begin First Task...");
GetLongestWord(words);
},
() =>
{
Console.WriteLine("Begin Second Task...");
GetMostCommonWords(words);
}, //close second action
() =>
{
Console.WriteLine("Begin Third Task...");
GetCountForWord(words, "Sleep");
} //close third action
); //close parallel.invoke
#endregion
}
public void getAllEmployee(int choice)
{
SqlConnection connection = new SqlConnection(connectionString);
try
{
EmployeeModel employeeModel = new EmployeeModel();
using (connection)
{
string query = "";
switch (choice)
{
case 1:
query = @"SELECT id,name,phone_number,address,department,gender,
basic_pay,deductions,taxable_pay,tax,net_pay,start_Date
FROM employee_payroll";
break;
case 2:
query = @"SELECT id,name,phone_number,address,department,gender,
basic_pay,deductions,taxable_pay,tax,net_pay,start_Date
FROM employee_payroll WHERE start_Date between CAST('2020-10-27' AS DATE) and GETDATE()";
break;
case 3:
query = @"SELECT SUM(net_pay), MAX(net_pay), MIN(net_pay),
AVG(net_pay), COUNT(name), gender FROM employee_payroll GROUP BY gender";
break;
default:
break;
}
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows && choice == 3)
{
while (dr.Read())
{
Console.WriteLine("Sum : {0}, Avg : {1}, Max : {2}, Min : {3}, Count : {4}, Gender : {5}", dr.GetDecimal(0), dr.GetDecimal(1), dr.GetDecimal(2), dr.GetDecimal(3), dr.GetInt32(4), Convert.ToChar(dr.GetString(5)));
Console.WriteLine("\n");
}
}
else if (dr.HasRows)
{
while (dr.Read())
{
employeeModel.EmployeeID = dr.GetInt32(0);
employeeModel.EmployeeName = dr.GetString(1);
employeeModel.PhoneNumber = dr.GetString(2);
employeeModel.Address = dr.GetString(3);
employeeModel.Department = dr.GetString(4);
employeeModel.Gender = Convert.ToChar(dr.GetString(5));
employeeModel.BasicPay = Convert.ToDouble(dr.GetDecimal(6));
employeeModel.Deductions = Convert.ToDouble(dr.GetDecimal(7));
employeeModel.TaxablePay = Convert.ToDouble(dr.GetDecimal(8));
employeeModel.Tax = Convert.ToDouble(dr.GetDecimal(9));
employeeModel.NetPay = Convert.ToDouble(dr.GetDecimal(10));
employeeModel.StartDate = dr.GetDateTime(11);
Console.WriteLine("{0}, {1}, {2}, {3}, {4}", employeeModel.EmployeeID, employeeModel.EmployeeName, employeeModel.Address, employeeModel.Department, employeeModel.BasicPay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No data found");
}
dr.Close();
connection.Close();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
connection.Close();
}
}
static void Main(string[] args)
{
Console.WriteLine("Welcome To Employee Payroll Service System");
EmployeeRepo repo = new EmployeeRepo();
int loop = 1;
while (loop == 1)
{
Console.WriteLine("Choose \n1. View all records \n2. Add record \n3. Update salary \n4. Retrieve information from name \n5. Retrieve Employees with joining date in a range \n6. Sum of basic pay gender wise \n7. Average of basic pay gender wise \n8. Minimum basic pay gender wise \n9. Maximum basic pay gender wise \n10. Count of employees gender wise \n11. Remove emloyee from active employees \n12. Exit");
int choice = Convert.ToInt32(Console.ReadLine());
switch (choice)
{
case 1:
List <EmployeeModel> employeeList = repo.GetAllEmployee();
foreach (EmployeeModel employeeModel in employeeList)
{
System.Console.WriteLine(employeeModel.EmployeeName + " " + employeeModel.BasicPay + " " + employeeModel.StartDate + " " + employeeModel.Gender + " " + employeeModel.PhoneNumber + " " + employeeModel.Address + " " + employeeModel.Department + " " + employeeModel.Deductions + " " + employeeModel.TaxablePay + " " + employeeModel.Tax + " " + employeeModel.NetPay);
System.Console.WriteLine("\n");
}
break;
case 2:
Console.WriteLine("Enter number of employees to be added");
int noOfEmployeesAdded = Convert.ToInt32(Console.ReadLine());
List <EmployeeModel> list = new List <EmployeeModel>();
while (noOfEmployeesAdded >= 1)
{
EmployeeModel employee = new EmployeeModel();
Console.WriteLine("Enter Name");
employee.EmployeeName = Console.ReadLine();
Console.WriteLine("Enter Phone Number");
employee.PhoneNumber = Console.ReadLine();
Console.WriteLine("Enter Address");
employee.Address = Console.ReadLine();
Console.WriteLine("Enter Gender");
employee.Gender = Convert.ToChar(Console.ReadLine());
Console.WriteLine("Enter Basic Pay");
employee.BasicPay = Convert.ToDecimal(Console.ReadLine());
Console.WriteLine("Enter Start date");
employee.StartDate = Convert.ToDateTime(Console.ReadLine());
Console.WriteLine("Enter Department");
employee.Department = Console.ReadLine();
Console.WriteLine("Enter department id");
employee.DepartmentId = Convert.ToInt32(Console.ReadLine());
noOfEmployeesAdded--;
list.Add(employee);
}
noOfEmployeesAdded = repo.AddMultipleEmployeesUsingThread(list);
break;
case 3:
Console.WriteLine("Enter Name");
string name = Console.ReadLine();
Console.WriteLine("Enter Basic Pay");
double salary = Convert.ToDouble(Console.ReadLine());
bool result = repo.UpdateSalary(name, salary);
Console.WriteLine(result == true ? "Salary Updated" : "Salary cannot be updated");
break;
case 4:
Console.WriteLine("Enter Name");
string employeeName = Console.ReadLine();
List <EmployeeModel> employeeNameList = repo.RetrieveDataByName(employeeName);
foreach (EmployeeModel employeeInfo in employeeNameList)
{
System.Console.WriteLine(employeeInfo.EmployeeName + " " + employeeInfo.BasicPay + " " + employeeInfo.StartDate + " " + employeeInfo.Gender + " " + employeeInfo.PhoneNumber + " " + employeeInfo.Address + " " + employeeInfo.Department + " " + employeeInfo.Deductions + " " + employeeInfo.TaxablePay + " " + employeeInfo.Tax + " " + employeeInfo.NetPay);
System.Console.WriteLine("\n");
}
break;
case 5:
Console.WriteLine("Enter Start date");
DateTime startDate = Convert.ToDateTime(Console.ReadLine());
Console.WriteLine("Enter End date");
DateTime endDate = Convert.ToDateTime(Console.ReadLine());
List <EmployeeModel> employeeWithStartDateList = repo.RetrieveEmployeesWithParticularDateRange(startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
foreach (EmployeeModel employeeModel in employeeWithStartDateList)
{
System.Console.WriteLine(employeeModel.EmployeeName + " " + employeeModel.BasicPay + " " + employeeModel.StartDate + " " + employeeModel.Gender + " " + employeeModel.PhoneNumber + " " + employeeModel.Address + " " + employeeModel.Department + " " + employeeModel.Deductions + " " + employeeModel.TaxablePay + " " + employeeModel.Tax + " " + employeeModel.NetPay);
System.Console.WriteLine("\n");
}
break;
case 6:
repo.SumOfSalaryGenderWise();
break;
case 7:
repo.AverageOfSalaryGenderWise();
break;
case 8:
repo.MinimumSalaryGenderWise();
break;
case 9:
repo.MaximumSalaryGenderWise();
break;
case 10:
repo.CountOfEmployeesGenderWise();
break;
case 11:
Console.WriteLine("Enter employee Id of employee to be removed");
int empId = Convert.ToInt32(Console.ReadLine());
bool employeeRemoved = repo.RemoveEmployee(empId);
if (employeeRemoved)
{
Console.WriteLine("Employee Removed successfully");
}
break;
case 12:
loop = 0;
break;
}
}
}
public EmployeePayroll()
{
employeeModel = new EmployeeModel();
}
public void AddEmployeePayroll(EmployeeModel employeeModel)
{
///Thread.Sleep(100)
employeePayrollDataList.Add(employeeModel);
}
