//the function to load the slip details for the employee
private void LoadSlip()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (empCode != null && employeeNameLabel.Text != "")
{
string getSlipDetails = "select * from payroll where empCode = '" + empCode + "' and dateGenerated = '" + payRollDate.Text + "'";
MySqlCommand com = new MySqlCommand(getSlipDetails, con);
MySqlDataAdapter da;
DataTable table = new DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(com);
da.Fill(table);
da.Dispose();
if (table.Rows.Count > 0)
{
checkReceipt = true;
string getYear = "select dateRegistered from employee where empCode = '" + table.Rows[0][3].ToString() + "' AND STATE = 'ACTIVE'";
MySqlCommand com2 = new MySqlCommand(getYear, con);
DataTable tab = new DataTable();
da = new MySqlDataAdapter(com2);
da.Fill(tab); da.Dispose();
int yearOfService;
if (tab.Rows[0][0].ToString().Substring(4, 1) == "/")
{
yearOfService = DateTime.Now.Year - int.Parse(tab.Rows[0][0].ToString().Substring(0, 4));
}
else
{
yearOfService = DateTime.Now.Year - int.Parse(tab.Rows[0][0].ToString().Substring(6));
}
foreach (var process in System.Diagnostics.Process.GetProcesses(Environment.MachineName))
{
if (process.MainWindowTitle.Contains("receipt.pdf"))
{
process.Kill();
break;
}
}
salarySlip.PreapareSalarySlip(table.Rows[0][2].ToString(),
table.Rows[0][3].ToString(),
table.Rows[0][5].ToString(),
table.Rows[0][37].ToString(),
table.Rows[0][7].ToString(),
string.Format("{0:n}", table.Rows[0][6]),
yearOfService.ToString(),
payRollDate.Text,
string.Format("{0:n}", table.Rows[0][10]),
string.Format("{0:n}", table.Rows[0][11]),
string.Format("{0:n}", table.Rows[0][12]),
string.Format("{0:n}", table.Rows[0][13]),
string.Format("{0:n}", table.Rows[0][14]),
string.Format("{0:n}", table.Rows[0][15]),
string.Format("{0:n}", table.Rows[0][16]),
string.Format("{0:n}", table.Rows[0][17]),
string.Format("{0:n}", table.Rows[0][18]),
string.Format("{0:n}", table.Rows[0][20]),
string.Format("{0:n}", table.Rows[0][19]),
string.Format("{0:n}", table.Rows[0][21]),
string.Format("{0:n}", table.Rows[0][22]),
string.Format("{0:n}", table.Rows[0][23]),
string.Format("{0:n}", table.Rows[0][24]),
string.Format("{0:n}", table.Rows[0][25]),
string.Format("{0:n}", table.Rows[0][26]),
string.Format("{0:n}", table.Rows[0][27]),
string.Format("{0:n}", table.Rows[0][30]),
string.Format("{0:n}", table.Rows[0][29]),
string.Format("{0:n}", table.Rows[0][28]),
string.Format("{0:n}", table.Rows[0][31]),
string.Format("{0:n}", table.Rows[0][32]),
string.Format("{0:n}", table.Rows[0][33]),
string.Format("{0:n}", table.Rows[0][39]),
string.Format("{0:n}", table.Rows[0][34]),
string.Format("{0:n}", table.Rows[0][35]),
string.Format("{0:n}", table.Rows[0][36]));
messagelabel.Text = "Please Wait, the receipt will open in another application. To send the slip through email, press 'Send Email' button.";
System.Diagnostics.Process.Start("C:/Users/" + Home.computerName + "/AppData/Roaming/SEC Payroll/Receipts/receipt.pdf");
// messagelabel.Text = "The slip has been created press 'Send Email' button to send it through email.";
Login.RecordUserActivity("Viewed or (probably) Printed the salary slip for " + table.Rows[0][2].ToString() + " of " + table.Rows[0][3].ToString() + " as Employee ID");
}
else
{
MessageBox.Show("Sorry, this Employee is not pressent in this payroll Month. Please check for another Month.");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
else
{
MessageBox.Show("Please select the name of an employee to view the Salary Slip");
}
}
private void calculationAndGeneration()
{
using (MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = Home.DBconnection;
string getEmployeeDetails = "select * from employee where STATE = 'ACTIVE'";
//checking if the payroll is present
string checkThePayroll = "select dateGenerated from payRoll where dateGenerated = '" + payRollDate.Text + "'";
MySqlCommand CheckPayrol = new MySqlCommand(checkThePayroll, con);
System.Data.DataTable tab = new System.Data.DataTable();
MySqlCommand EmployeeCom = new MySqlCommand(getEmployeeDetails, con);
MySqlDataAdapter da;
MySqlDataReader rd;
System.Data.DataTable Emptable = new System.Data.DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(CheckPayrol);
da.Fill(tab);
da.Dispose();
if (tab.Rows.Count > 0)
{
payRollDataGrid.DataSource = null;
MessageBox.Show("The Pay-Roll already existed.");
}
else
{
//table for Employees
da = new MySqlDataAdapter(EmployeeCom);
da.Fill(Emptable);
da.Dispose();
if (Emptable.Rows.Count > 0)
{
for (int i = 0; i < Emptable.Rows.Count; i++)
{
resetVariables();
salaryBasic = double.Parse(Emptable.Rows[i][8].ToString()) * (double.Parse(Emptable.Rows[i][9].ToString()) / 100);
allowanceUtility = salaryBasic * 1.5 / 12;
//geting the allowances for the employee
string getAllowance = "select * from employeeallowance where empCode = '" + Emptable.Rows[i][4] + "'";
MySqlCommand allowCom = new MySqlCommand(getAllowance, con);
System.Data.DataTable allowTable = new System.Data.DataTable();
da = new MySqlDataAdapter(allowCom);
da.Fill(allowTable);
da.Dispose();
double allowanceTotal = 0.00;
//total allowance calculations
for (int j = 0; j < allowTable.Rows.Count; j++)
{
allowanceTotal += double.Parse(allowTable.Rows[j][3].ToString());
//Get the Name of the allowance
string selectAllowance = "select allowanceName from allowance where allowanceID = '" + allowTable.Rows[j][2] + "' ";
MySqlCommand allowCom1 = new MySqlCommand(selectAllowance, con);
System.Data.DataTable allowTable1 = new System.Data.DataTable();
da = new MySqlDataAdapter(allowCom1);
da.Fill(allowTable1);
da.Dispose();
for (int x = 0; x < allowTable1.Rows.Count; x++)
{
//for Auto Depreciation
if (allowTable1.Rows[x][0].ToString() == "AUTO DEPRECIATION")
{
allowanceAutDepr = (double)allowTable.Rows[j][3];
}
//Travel D/Leaders
if (allowTable1.Rows[x][0].ToString() == "TRAVEL D/LEADER")
{
travelDLeaders = (double)allowTable.Rows[j][3];
}
//Travel C/Limit
if (allowTable1.Rows[x][0].ToString() == "TRAVEL C/LIMIT")
{
travelCLimit = (double)allowTable.Rows[j][3];
}
//Postage Exps
if (allowTable1.Rows[x][0].ToString() == "POSTAGE EXPS")
{
postageExps = (double)allowTable.Rows[j][3];
}
//Bicycle Allowance
if (allowTable1.Rows[x][0].ToString() == "BICYCLE ALLOWANCE")
{
bicycleAllowance = (double)allowTable.Rows[j][3];
}
}
}
incomeTotal = salaryBasic + allowanceTotal + allowanceUtility;
//geting the deductions for the employee
string getDeduction = "select * from employeededuction where empCode = '" + Emptable.Rows[i][4].ToString() + "' and dateForDeduction = '" + payRollDate.Text + "' and statuse <> 'YES'";
MySqlCommand deductionCom = new MySqlCommand(getDeduction, con);
System.Data.DataTable deductionTable = new System.Data.DataTable();
da = new MySqlDataAdapter(deductionCom);
da.Fill(deductionTable);
da.Dispose();
double dductionTotal = 0.00;
for (int k = 0; k < deductionTable.Rows.Count; k++)
{
dductionTotal += double.Parse(deductionTable.Rows[k][3].ToString());
//get the name of deduction
string getDeductionName = "select deductionname from deduction where deductionID = '" + deductionTable.Rows[k][2].ToString() + "'";
MySqlCommand deductionCom1 = new MySqlCommand(getDeductionName, con);
System.Data.DataTable deductionTable1 = new System.Data.DataTable();
da = new MySqlDataAdapter(deductionCom1);
da.Fill(deductionTable1);
da.Dispose();
for (int y = 0; y < deductionTable1.Rows.Count; y++)
{
if (deductionTable1.Rows[y][0].ToString() == "Maafa Fund")
{
maafaFund = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "Food")
{
food = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "SACCOS")
{
SACCOS = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "Cash and Salary Advance")
{
salaryAdvance = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "NBC Loan")
{
NBCLoan = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "Motor Vehicle Loan")
{
motorVehicleLoan = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "Furniture Loan")
{
furnitureLoan = (double)deductionTable.Rows[k][3];
}
if (deductionTable1.Rows[y][0].ToString() == "Saving P/ Service")
{
personalSaving = (double)deductionTable.Rows[k][3];
}
}
}
//check if an employee has social security fund, house rent and HESLB deduction
string checkStatusForDEduction = "select deductionID,percentage from employeededuction where statuse = 'YES' and dateForDeduction = '" + payRollDate.Text + "' and empCode = '" + Emptable.Rows[i][4].ToString() + "'";
//string checkStatusForDEduction = "select concat(fname,' ',lname),deductionID,percentage from employee join employeededuction on employee.empCode = employeededuction.empCode where employeededuction.statuse = 'YES' and employeededuction.dateForDeduction = '"+ payRollDate.Text + "'";
MySqlCommand deductionCom2 = new MySqlCommand(checkStatusForDEduction, con);
System.Data.DataTable deductionTableStatus = new System.Data.DataTable();
da = new MySqlDataAdapter(deductionCom2);
da.Fill(deductionTableStatus);
da.Dispose();
if (deductionTableStatus.Rows.Count > 0)
{
for (int s = 0; s < deductionTableStatus.Rows.Count; s++)
{
//get the name of deduction
string getDeductionName2 = "select deductionname from deduction where deductionID = '" + deductionTableStatus.Rows[s][0].ToString() + "'";
MySqlCommand deductionCom12 = new MySqlCommand(getDeductionName2, con);
System.Data.DataTable deductionTable12 = new System.Data.DataTable();
da = new MySqlDataAdapter(deductionCom12);
da.Fill(deductionTable12);
da.Dispose();
for (int p = 0; p < deductionTable12.Rows.Count; p++)
{
switch (deductionTable12.Rows[p][0].ToString())
{
case "Social Security Fund":
socialSecurityFund = incomeTotal * (double.Parse(deductionTableStatus.Rows[s][1].ToString()) / 100);
break;
case "House Rent":
houseRent = salaryBasic * (double.Parse(deductionTableStatus.Rows[s][1].ToString()) / 100);
break;
case "HESLB Loan 15%":
HESLBLoan = salaryBasic * (double.Parse(deductionTableStatus.Rows[s][1].ToString()) / 100);
break;
case "Deduction On Account":
deductionOnAccount = salaryBasic * (double.Parse(deductionTableStatus.Rows[s][1].ToString()) / 100);
break;
case "NHIF":
NHIF = salaryBasic * (double.Parse(deductionTableStatus.Rows[s][1].ToString()) / 100);
break;
default:
break;
}
}
}
}
incomeTaxable = salaryBasic + allowanceUtility - socialSecurityFund;
if (Emptable.Rows[i][17].ToString() == "TOTAL INCOME")
{
tithe = incomeTotal * 0.1;
}
else
{
tithe = salaryBasic * 0.1;
}
if (incomeTaxable < 170000)
{
i_Tax = 0;
}
if (incomeTaxable < 360000)
{
i_Tax = 0.09 * (incomeTaxable - 170000);
}
if (incomeTaxable < 540000)
{
i_Tax = 17100 + 0.2 * (incomeTaxable - 360000);
}
if (incomeTaxable < 720000)
{
i_Tax = 53100 + 0.25 * (incomeTaxable - 540000);
}
else
{
i_Tax = 98100 + 0.3 * (incomeTaxable - 720000);
}
deductionTotal = dductionTotal + socialSecurityFund + deductionOnAccount + HESLBLoan + houseRent + NHIF + tithe + i_Tax;
salaryNet = incomeTotal - deductionTotal;
refund_iTax = i_Tax * 0.9;
payNet = salaryNet + refund_iTax;
string empFullName = Emptable.Rows[i][1] + " " + Emptable.Rows[i][2] + " " + Emptable.Rows[i][3];
string values = "values('" + payRollDate.Text + "','" + empFullName + "','" + Emptable.Rows[i][4] + "','" + Emptable.Rows[i][5] +
"', '" + Emptable.Rows[i][7] + "','" + Emptable.Rows[i][8].ToString() + "','" + Emptable.Rows[i][9].ToString() + "','" + Emptable.Rows[i][13] +
"', '" + Emptable.Rows[i][14] + "','" + salaryBasic + "','" + allowanceAutDepr + "','" + bicycleAllowance + "','" + travelDLeaders + "','" + travelCLimit + "','" + postageExps +
"','" + allowanceUtility + "','" + incomeTotal + "','" + incomeTaxable + "','" + tithe +
"','" + i_Tax + "','" + socialSecurityFund + "','" + houseRent + "','" + maafaFund + "','" + food + "','" + SACCOS + "','" + salaryAdvance + "','" + NBCLoan +
"','" + motorVehicleLoan + "','" + furnitureLoan + "','" + HESLBLoan + "','" + deductionOnAccount + "','" + personalSaving + "','" + NHIF + "','" + salaryNet +
"','" + refund_iTax + "','" + payNet + "','" + Emptable.Rows[i][12] + "', '" + Emptable.Rows[i][10] + "','" + deductionTotal + "')";
string insertPayRoll = "insert into payRoll(dateGenerated,empFullName,empCode," +
" deptCode,salaryCategory,ScaleBase,scalePercent,bankAccount,bankName,salaryBasic," +
"allowanceAutoDepr,allowanceBicycle,LeadertravelAll,cLimitTravel,postageExps," +
"allowanceUtility,incomeTotal,incomeTaxable,tithe,iTax,socialSecurityFund,Houserent," +
"MaafaFund,food,SACCOS,cashAndSalaryAdvance,NBC,motorVehicleLoan,furnitureLoan," +
"HESLB,deductionOnReport,personalServing,NHIF,salaryNet,refunDiTax," +
"payNet,position,email,totalDeduction) " + values;
using (MySqlCommand generatePayR = new MySqlCommand(insertPayRoll, con))
{
rd = generatePayR.ExecuteReader();
rd.Close();
}
}
Login.RecordUserActivity("Genarated the payroll");
//show the Pay-Roll
getGeneratedPayRoll();
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Clone();
}
}