private void sendEmail(string email, string message)
{
using (MailMessage mail = new MailMessage("*****@*****.**", email, "TESTING THE SALARY SLIP EMAIL SENDER", message))
using (SmtpClient client = new SmtpClient("smtp.gmail.com"))
using (Attachment file = new Attachment("C:/Users/" + Home.computerName + "/AppData/Roaming/SEC Payroll/Receipts/receipt.pdf"))
{
client.Port = 587;
client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "secadventist01");
client.EnableSsl = true;
file.Name = "Salary Slip for " + payRollDate.Text + ".pdf";
mail.Attachments.Add(file);
try
{
client.Send(mail);
Login.RecordUserActivity("Sent the Salary Slip to " + email);
MessageBox.Show("Email Sent");
}
catch
{
MessageBox.Show("Email Not Sent");
}
}
}
private void sendEmail(string email, string message)
{
sendInfo = new Label();
sendInfo.Font = new Font("Calibri", 11);
sendInfo.AutoSize = true;
using (MailMessage mail = new MailMessage("*****@*****.**", email, "TESTING THE SALARY SLIP EMAIL SENDER", message))
using (SmtpClient client = new SmtpClient("smtp.gmail.com"))
using (Attachment file = new Attachment("C:/Users/" + Home.computerName + "/AppData/Roaming/SEC Payroll/Receipts/receipt.pdf"))
{
client.Port = 587;
client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "secadventist01");
client.EnableSsl = true;
file.Name = "Salary Slip for " + receiptDate.Text + ".pdf";
mail.Attachments.Add(file);
try
{
client.Send(mail);
Login.RecordUserActivity("Sent the Salary Slip to " + email);
sendInfo.ForeColor = Color.LightGreen;
sendInfo.Text = "Email sent to: " + email + " (" + DateTime.Now.ToLongTimeString() + ")";
information.Controls.Add(sendInfo);
}
catch
{
sendInfo.ForeColor = Color.Orange;
sendInfo.Text = "Email NOT sent to: " + email + " (" + DateTime.Now.ToLongTimeString() + ")";
information.Controls.Add(sendInfo);
}
}
}
//a function to update the allowance name
private void updateAllowanceBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (allowanceNumber != null)
{
string updateAllowance = "update allowance set allowanceName = '" + editedAllowanceTxt.Text.ToUpper() + "' where allowanceID = '" + allowanceNumber + "'";
MySqlCommand com = new MySqlCommand(updateAllowance, con);
MySqlDataReader rd;
try
{
con.Open();
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changeg Allowance Name From " + allName + " to " + editedAllowanceTxt.Text.ToUpper());
loadAllTimer.Start();
allowanceNumber = null;
editedAllowanceTxt.Text = "";
MessageBox.Show("Allowance updated");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
else
{
MessageBox.Show("Please select the Allowance from the list");
}
}
private void addAllowanceBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (check == true)
{
AllowanceAllocateForStaff();
}
else
{
if (allowanceID != null && empCode != null && allowanceAmountTxt.Text != "")
{
string checkAllowance = "select * from employeeallowance where empCode = '" + empCode + "' and allowanceID = '" + allowanceID + "'";
string insert = "insert into employeeallowance(empCode,allowanceID,amount) " +
" values('" + empCode + "','" + allowanceID + "','" + allowanceAmountTxt.Text + "')";
MySqlCommand com = new MySqlCommand(insert, con);
MySqlCommand comCheck = new MySqlCommand(checkAllowance, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
try
{
con.Open();
rd = comCheck.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
MessageBox.Show("This Allowance is Aready Added for this Employee.");
}
else
{
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Added Allowance " + allowanceCombo.Text + " of amount " + allowanceAmountTxt.Text + " to employee " + empCode);
allowanceAmountTxt.Text = "";
all = true;
ded = false;
successTimer.Start();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("EmpCode or AllowanceID or Empty amount Error ");
}
}
}
private void DedDeleteBtn_Click(object sender, EventArgs e)
{
if (dedID != null && deductionCombo.Text != "")
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string delete = "delete from employeededuction where ID = '" + dedID + "'";
MySqlCommand com = new MySqlCommand(delete, con);
MySqlDataReader rd;
try
{
con.Open();
if (MessageBox.Show("Are you sure you want to delete this deduction for " + EmployeeFullName, "Alart!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Deleted " + deductionCombo.Text + " Deduction for Employee " + EmployeeFullName + " of employeeID " + empCode + "");
deductionCombo.Items.Clear();
GetDetails(EmployeeFullName);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please select the name from the list and select deduction from the list to perform this operation", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void updateDeductionBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (deductionNumber != null)
{
string updateDeduction = "update deduction set deductionName = '" + editedDeductionTxt.Text.ToUpper() + "' where deductionID = '" + deductionNumber + "'";
MySqlCommand com = new MySqlCommand(updateDeduction, con);
MySqlDataReader rd;
try
{
con.Open();
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed " + dedName + " deduction to " + editedDeductionTxt.Text.ToUpper());
loadAllTimer.Start();
deductionNumber = null;
editedDeductionTxt.Text = "";
MessageBox.Show("Deduction updated");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
else
{
MessageBox.Show("Please select the Deduction from the list");
}
}
private void updateUserBtn_Click(object sender, EventArgs e)
{
if (firstNameTxt.Text == "" ||
lastNameTxt.Text == "" ||
userNameTxt.Text == "" ||
passwordTxt.Text == ""
)
{
MessageBox.Show("Please, fill all the fields..!");
}
else if (userCategory.selectedIndex == -1)
{
MessageBox.Show("Please, select category for a user.");
}
else if (empID == null)
{
MessageBox.Show("Please, select a User for updating.!");
}
else
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string updateUser = "******" + firstNameTxt.Text.ToUpper() + "', mname = '" + middleNameTxt.Text.ToUpper() +
"', lname = '" + lastNameTxt.Text.ToUpper() +
"', username = '******', password = '******', userCategory = '" + userCategory.selectedValue.ToString().ToUpper() +
"' where UID = '" + empID + "'";
MySqlCommand com = new MySqlCommand(updateUser, con);
MySqlDataReader rd;
try
{
con.Open();
//update an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed user information for user Id " + empID);
LoadEmployee();
empID = null;
firstNameTxt.Text = "";
middleNameTxt.Text = "";
lastNameTxt.Text = "";
userNameTxt.Text = "";
passwordTxt.Text = "";
MessageBox.Show("User Updated Successful");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
}
private void closeBtn_MouseClick(object sender, MouseEventArgs e)
{
if (e.Button == MouseButtons.Left)
{
Login.RecordUserActivity("Logout");
this.Close();
Login log = new Login();
log.Show();
}
}
private void deleteBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string checkPay = "select * from payroll where dateGenerated = '" + payRollDate.Text + "'";
MySqlCommand payroll = new MySqlCommand(checkPay, con);
MySqlDataAdapter da;
System.Data.DataTable payRollTable = new System.Data.DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(payroll);
da.Fill(payRollTable);
da.Dispose();
if (payRollTable.Rows.Count > 0)
{
if (MessageBox.Show("Are you sure you want to delete this payroll?", "Alert", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
for (int i = 0; i < payRollTable.Rows.Count; i++)
{
string delete = "update payroll set dateGenerated = 'DELETED' where payRollID = '" + payRollTable.Rows[i][0].ToString() + "' ";
MySqlCommand deletepay = new MySqlCommand(delete, con);
MySqlDataReader rd;
rd = deletepay.ExecuteReader();
rd.Close();
}
Login.RecordUserActivity("Deleted the payroll for date " + payRollDate.Text + "");
MessageBox.Show("Payroll deletes successful.");
}
else
{
}
}
else
{
MessageBox.Show("The payroll does not exist, please select the correct date.");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void getGeneratedPayRoll()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string loadPayRoll = "select empFullName 'Employee Name',empCode 'Employee ID'," +
" deptCode 'Depertment ID',salaryCategory 'Salary Category',ScaleBase 'Scale Base',scalePercent 'Scale Percent',bankAccount 'Bank Account',bankName 'Bank Name',salaryBasic 'Salary Basic'," +
"allowanceAutoDepr 'Allowance Depression',allowanceBicycle 'Allowance Bicycle',LeadertravelAll 'Leader Travel Allowance',cLimitTravel 'Travel C/Limit', postageExps 'Postage Exps'," +
"allowanceUtility 'Allowance Utility',IncomeTaxable 'Income Taxable',tithe 'Tithe',iTax 'i/Tax',socialSecurityFund 'Social Security Fund',Houserent 'House Rent'," +
"MaafaFund 'Maafa Fund',food 'Food',SACCOS,cashAndSalaryAdvance 'Cash/Salary Advance',NBC,motorVehicleLoan 'Motor Vehicle Loan',furnitureLoan 'Furniture Loan'," +
"HESLB 'HESLB 15%',deductionOnReport 'Deduction on Report',personalServing 'Personal Serving',NHIF,salaryNet 'Sarary Net',refunDiTax 'Refund Tax'," +
"payNet 'Pay Net',position Position,email 'Employee Email' from payroll where dateGenerated = '" + payRollDate.Text + "'";
MySqlCommand payroll = new MySqlCommand(loadPayRoll, con);
MySqlDataAdapter da;
System.Data.DataTable payRollTable = new System.Data.DataTable();
try
{
da = new MySqlDataAdapter(payroll);
da.Fill(payRollTable);
da.Dispose();
if (payRollTable.Rows.Count > 0)
{
for (int i = 0; i < payRollTable.Columns.Count; i++)
{
try
{
payRollDataGrid.Columns[i].DefaultCellStyle.Format = "N2";
}
catch
{
}
}
payRollDataGrid.DataSource = payRollTable;
Login.RecordUserActivity("Viewed the payroll");
}
else
{
payRollDataGrid.DataSource = null;
MessageBox.Show("The Payroll is not Found.");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void deleteEmpBtn_Click(object sender, EventArgs e)
{
bool check = false;
if (empID != null && MessageBox.Show("Are you sure you want to remove this Employee?", "Alert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
//string delete = "SET foreign_key_checks = 0;"+
// " DELETE a.*, e.*, d.* "+
// " FROM employee e "+
// " JOIN employeeallowance a ON a.empCode = e.empCode "+
// " JOIN employeededuction d ON d.empCode = a.empCode "+
// " WHERE d.empCode = '"+empCodeTxt.Text+"';"+
// " SET foreign_key_checks = 1;";
string delete = "SET foreign_key_checks = 0;" +
" UPDATE employee set state = 'DEACTIVE'" +
" WHERE empCode = '" + empCodeTxt.Text + "';" +
" SET foreign_key_checks = 1;";
MySqlCommand com = new MySqlCommand(delete, con);
MySqlDataReader rd;
try
{
con.Open();
//delete an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Deactivated " + empname + " who had " + empCode + " as employee ID ");
clearFields();
LoadEmployee();
empID = null;
MessageBox.Show("Employee Removed Successful");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
check = true;
}
if (empID == null && check == false)
{
MessageBox.Show("Please, select the name of an employee before deletion!");
}
}
private void addUserBtn_Click(object sender, EventArgs e)
{
if (firstNameTxt.Text == "" ||
lastNameTxt.Text == "" ||
userNameTxt.Text == "" ||
passwordTxt.Text == "")
{
MessageBox.Show("Please, select an employee from the list");
}
else if (userCategory.selectedIndex == -1)
{
MessageBox.Show("Please, select category for a user.");
}
else
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string value = "values('" + firstNameTxt.Text.ToUpper() +
"','" + middleNameTxt.Text.ToUpper() +
"','" + lastNameTxt.Text.ToUpper() +
"','" + userNameTxt.Text.ToUpper() +
"','" + Login.GetMD5Hash(passwordTxt.Text) +
"','" + DateTime.Now +
"','" + userCategory.selectedValue.ToUpper() + "')";
string registerUser = "******" + value;
MySqlCommand com = new MySqlCommand(registerUser, con);
MySqlDataReader rd;
try
{
con.Open();
//registering an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Registered " + firstNameTxt.Text.ToUpper() + " " + middleNameTxt.Text.ToUpper() + " " + lastNameTxt.Text.ToUpper() + " As a new user to the system");
MessageBox.Show("User Added Successful");
clearFields();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
}
private void verifyBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (password.Text == "")
{
MessageBox.Show("Please enter password.");
}
else
{
string loadUser = "******" + Login.UID + "' and password = '******'";
MySqlCommand com = new MySqlCommand(loadUser, con);
MySqlDataAdapter da;
DataTable tab = new DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(com);
da.Fill(tab);
da.Dispose();
if (tab.Rows.Count > 0)
{
Login.RecordUserActivity("Login");
this.Close();
Home home = new Home();
home.ShowDialog();
}
else
{
MessageBox.Show("Wrong Password");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
}
private void deleteUserBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string delete = " delete from users where UID = '" + empID + "'";
MySqlCommand com = new MySqlCommand(delete, con);
MySqlDataReader rd;
try
{
con.Open();
if (empID != null)
{
if (MessageBox.Show("Are sure you want to delete this user?", "Alert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//delete an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Deleted User " + firstNameTxt.Text + " " + middleNameTxt.Text + " " + lastNameTxt.Text + " from the system");
LoadEmployee();
empID = null;
firstNameTxt.Text = "";
middleNameTxt.Text = "";
lastNameTxt.Text = "";
userNameTxt.Text = "";
passwordTxt.Text = "";
MessageBox.Show("User Deleted Successful");
}
}
else
{
MessageBox.Show("Please Select User to delete");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
private void addAllowanceBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (!string.IsNullOrWhiteSpace(newAllowanceTxt.Text))
{
string registerNewAllowance = "insert into allowance(allowanceName) values('" + newAllowanceTxt.Text.ToUpper() + "')";
MySqlCommand com = new MySqlCommand(registerNewAllowance, con);
MySqlDataReader rd;
try
{
con.Open();
//register the new Allowance
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Registered " + newAllowanceTxt.Text.ToUpper() + " Allowance");
loadAllTimer.Start();
newAllowanceTxt.Text = "";
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please Write the name of the new Allowance in the Textbox.");
}
}
private void addDepartmentBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (!string.IsNullOrWhiteSpace(newDepartmentTxt.Text) && !string.IsNullOrWhiteSpace(deptCode.Text))
{
string registerNewDepartment = "insert into department(deptCode,deptName) values('SEC-" + deptCode.Text.ToUpper() + "','" + newDepartmentTxt.Text.ToUpper() + "')";
MySqlCommand com = new MySqlCommand(registerNewDepartment, con);
MySqlDataReader rd;
try
{
con.Open();
//register the new Department
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Registerd " + newDepartmentTxt.Text.ToUpper() + " Department");
loadAllTimer.Start();
newDepartmentTxt.Text = "";
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please Write the name of the new Department and its ID on the textboxes");
}
}
//update allowance for an Employee
private void AllowUpdateBtn_Click(object sender, EventArgs e)
{
if (allowanceAmountTxt.Text != "" && allowanceCombo.Text != "" && AllowID != null)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string update = "update employeeallowance set amount = '" + allowanceAmountTxt.Text + "' where ID = '" + AllowID + "' ";
MySqlCommand com = new MySqlCommand(update, con);
MySqlDataReader rd;
try
{
con.Open();
//update
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed " + allowanceCombo.Text + " Allowance amount from " + allowanceamount + " to " + allowanceAmountTxt.Text + " for Employee " + EmployeeFullName + " of employeeID " + empCode + "");
MessageBox.Show("Update Successful!");
allowanceAmountTxt.Text = "";
allowanceCombo.Text = "";
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please select the name from the list and select allowance from the list to perform this operation", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void btn_Click(object sender, EventArgs e)
{
var button = sender as BunifuFlatButton;
if (MessageBox.Show("Are you sure you want to restore this Employee?", "Alert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string restore = "SET foreign_key_checks = 0;" +
" UPDATE employee set state = 'ACTIVE'" +
" WHERE empID = '" + button.Name + "';" +
" SET foreign_key_checks = 1;";
MySqlCommand com = new MySqlCommand(restore, con);
MySqlDataReader rd;
try
{
con.Open();
//RESTORE an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Activated employee of empID " + button.Name + " ");
//REFRESH THE PAGE
getRemovedEmployees();
MessageBox.Show("Employee Restored Successful");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
}
private void deleteDepartmentBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (departmentNumber != null)
{
string deleteDepartment = "delete from department where deptID = '" + departmentNumber + "'";
MySqlCommand com = new MySqlCommand(deleteDepartment, con);
MySqlDataReader rd;
try
{
con.Open();
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Deleted the department " + deptName + " from the system");
loadAllTimer.Start();
departmentNumber = null;
editedDepartmentTxt.Text = "";
deptCode.Text = "";
MessageBox.Show("Department deleted Successful!");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
else
{
MessageBox.Show("Please select the Department from the list");
}
}
private void updateDepartmentBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (!string.IsNullOrWhiteSpace(deptCode.Text) && !string.IsNullOrWhiteSpace(editedDepartmentTxt.Text) && departmentNumber != null)
{
string updateDepartment = "update department set deptName = '" + editedDepartmentTxt.Text.ToUpper() + "', deptCode = 'SEC-" + deptCode.Text.ToUpper() + "' where deptID = '" + departmentNumber + "'";
MySqlCommand com = new MySqlCommand(updateDepartment, con);
MySqlDataReader rd;
try
{
con.Open();
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed Department name from " + deptName + " to " + editedDepartmentTxt.Text.ToUpper());
loadAllTimer.Start();
departmentNumber = null;
editedDepartmentTxt.Text = "";
deptCode.Text = "";
MessageBox.Show("Department updated");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
else
{
MessageBox.Show("Please select the Department from the list and make sure the fields are not empty.");
}
}
private void regiterEmpBtn_Click(object sender, EventArgs e)
{
if (firstNameTxt.Text == "" ||
lastNameTxt.Text == "" ||
empCodeTxt.Text == "" ||
salaryCategory.selectedValue == "" ||
scaleBaseTxt.Text == "" ||
scalePercentTxt.Text == "" ||
emailTxt.Text == "" ||
phoneNumberTxt.Text == "" ||
positionTxt.Text == "" ||
bankNameTxt.Text == "" ||
bankAcountNumberTxt.Text == "" ||
employeeStatus.SelectedIndex == 0 ||
deptCode == null ||
departmentCombo.SelectedIndex == -1 ||
deductTitheOnCombo.SelectedIndex == 0)
{
MessageBox.Show("Please, fill all the fields..!");
}
else
{
switch (emailAllowed)
{
case true: //the email is correct, register employee
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string value = "values('" + firstNameTxt.Text.ToUpper() + "','" + middleNameTxt.Text.ToUpper() +
"','" + lastNameTxt.Text.ToUpper() +
"','" + empCodeTxt.Text.ToUpper() +
"','" + deptCode + "','" + dateOfBirth.Text +
"','" + salaryCategory.selectedValue +
"','" + scaleBaseTxt.Text +
"','" + scalePercentTxt.Text +
"','" + emailTxt.Text +
"','" + phoneNumberTxt.Text +
"','" + positionTxt.Text.ToUpper() +
"','" + bankAcountNumberTxt.Text.ToUpper() +
"','" + bankNameTxt.Text.ToUpper() +
"','" + employedDate.Text +
"','" + employeeStatus.Text.ToUpper() +
"','" + deductTitheOnCombo.Text + "')";
string registerEmployee = "insert into employee(fname,mname,lname,empCode,DeptCode,DOB,salaryCategory,scaleBase,scalePercent,employeeEmail,phoneNumber,position,bankAccount,bankName,dateRegistered,statuse,deductTitheOn) " + value;
MySqlCommand com = new MySqlCommand(registerEmployee, con);
MySqlDataReader rd;
try
{
con.Open();
//registering an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Registered " + firstNameTxt.Text + " " + middleNameTxt.Text + " " + lastNameTxt.Text + " as a new Employee in the system");
MessageBox.Show("Employee Registered Successful");
clearFields();
}
catch (MySqlException ex) {
MessageBox.Show(ex.Message);
}
con.Close();
break;
case false:
//the email is not correct
MessageBox.Show("Please, enter the valid email.");
break;
}
}
}
private void changePassBtn_Click(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(Oldpassword.Text) || String.IsNullOrWhiteSpace(Newpassword.Text))
{
MessageBox.Show("Please fill all the field.");
}
else
{
if (Oldpassword.Text != Newpassword.Text)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string verify = "select password from users where password = '******'";
string update = "Update users set password = '******' where UID = '" + Login.UID + "'";
MySqlCommand com = new MySqlCommand(verify, con);
MySqlDataAdapter da;
MySqlDataReader rd;
DataTable tab = new DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(com);
da.Fill(tab);
da.Dispose();
if (tab.Rows.Count > 0)
{
if (Newpassword.Text == retypePassword.Text)
{
MySqlCommand com1 = new MySqlCommand(update, con);
rd = com1.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed password for userID " + Login.UID + "");
MessageBox.Show("Password Changed Successful.");
this.Close();
}
else
{
MessageBox.Show("The New password did not match with the Re-type new password.");
}
}
else
{
MessageBox.Show("Wrong Old Password");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Old Password can not be the same as New Password");
}
}
}
// a function to insert all deduction for staffs only
private void DeductionAllocateForStaff()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string loadStaffId = "select empCode from employee where statuse = 'STAFF' AND STATE = 'ACTIVE'";
MySqlCommand com = new MySqlCommand(loadStaffId, con);
MySqlDataAdapter da;
DataTable table = new DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(com);
da.Fill(table);
da.Dispose();
if (putYes == true && deductionID != null && percentageTxt.Text != "")
{
for (int i = 0; i < table.Rows.Count; i++)
{
string insert = "insert into employeededuction(empCode,deductionID,deductionType,statuse,dateForDeduction,percentage) " +
" values('" + table.Rows[i][0].ToString() + "','" + deductionID + "','" + deductionType.Text + "','" + yes + "','" + deductionDate.Text + "','" + percentageTxt.Text + "')";
string checkDeduction = "select * from employeededuction where empCode = '" + table.Rows[i][0].ToString() + "' and deductionID = '" + deductionID + "'";
MySqlCommand com1 = new MySqlCommand(insert, con);
MySqlCommand com2 = new MySqlCommand(checkDeduction, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
rd = com2.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
}
else
{
rd = com1.ExecuteReader();
rd.Close();
percentageTxt.Text = "";
Login.RecordUserActivity("Added deduction " + deductionCombo.Text + " to employee " + table.Rows[i][0].ToString());
}
}
}
else
{
if (deductionID != null && deductionAmountTxt.Text != "" && deductionType.Text != "")
{
for (int i = 0; i < table.Rows.Count; i++)
{
string insert = "insert into employeededuction(empCode,deductionID,amountDeducted,deductionType,dateForDeduction) " +
" values('" + table.Rows[i][0].ToString() + "','" + deductionID + "','" + deductionAmountTxt.Text + ",'" + deductionType.Text + "','" + deductionDate.Text + "')";
string checkDeduction = "select * from employeededuction where empCode = '" + table.Rows[i][0].ToString() + "' and deductionID = '" + deductionID + "'";
MySqlCommand com1 = new MySqlCommand(insert, con);
MySqlCommand com2 = new MySqlCommand(checkDeduction, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
rd = com2.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
}
else
{
rd = com1.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Added deduction " + deductionCombo.Text + " of amount " + deductionAmountTxt.Text + " to employee " + table.Rows[i][0].ToString());
}
}
deductionAmountTxt.Text = "";
ded = true;
all = false;
successTimer.Start();
}
else
{
MessageBox.Show("EmpCode or AllowanceID or Empty amount Error ");
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void addDeductionBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
if (check == true)
{
DeductionAllocateForStaff();
}
else
{
if (putYes == true && deductionID != null && empCode != null && percentageTxt.Text != "")
{
deductionDate.CustomFormat = "MMMM yyyy";
string insert = "insert into employeededuction(empCode,deductionID,deductionType,statuse,dateForDeduction,percentage) " +
" values('" + empCode + "','" + deductionID + "','" + deductionType.Text + "','" + yes + "','" + deductionDate.Text + "','" + percentageTxt.Text + "')";
string checkDeduction = "select * from employeededuction where empCode = '" + empCode + "' and deductionID = '" + deductionID + "' and dateForDeduction = '" + deductionDate.Text + "'";
MySqlCommand com = new MySqlCommand(insert, con);
MySqlCommand com1 = new MySqlCommand(checkDeduction, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
try
{
con.Open();
rd = com1.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
MessageBox.Show("This deduction on this date arleady exist for this employee");
}
else
{
rd = com.ExecuteReader();
rd.Close();
percentageTxt.Text = "";
Login.RecordUserActivity("Added deduction " + deductionCombo.Text + " to employee " + empCode);
deductionAmountTxt.Text = "";
ded = true;
all = false;
successTimer.Start();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
//for distinct deductions
if (deductionID != null && empCode != null && deductionAmountTxt.Text != "" && deductionType.SelectedIndex != 0)
{
deductionDate.CustomFormat = "MMMM yyyy";
string insert = "insert into employeededuction(empCode,deductionID,amountDeducted,deductionType,dateForDeduction) " +
" values('" + empCode + "','" + deductionID + "','" + deductionAmountTxt.Text + "','" + deductionType.Text + "','" + deductionDate.Text + "')";
string checkDeduction = "select * from employeededuction where empCode = '" + empCode + "' and deductionID = '" + deductionID + "' and dateForDeduction = '" + deductionDate.Text + "'";
MySqlCommand com = new MySqlCommand(insert, con);
MySqlCommand com1 = new MySqlCommand(checkDeduction, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
try
{
con.Open();
rd = com1.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
MessageBox.Show("This deduction on this date arleady exist for this employee");
}
else
{
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Added deduction " + deductionCombo.Text + " of amount " + deductionAmountTxt.Text + " to employee " + empCode);
deductionAmountTxt.Text = "";
ded = true;
all = false;
successTimer.Start();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
//for constant deductions
else if (deductionID != null && empCode != null && deductionAmountTxt.Text != "" && deductionDate.Visible != true && deductionType.SelectedIndex != 0)
{
deductionDate.CustomFormat = "MMMM yyyy";
string insert = "insert into employeededuction(empCode,deductionID,amountDeducted,deductionType) " +
" values('" + empCode + "','" + deductionID + "','" + deductionAmountTxt.Text + "','" + deductionType.Text + "')";
string checkDeduction = "select * from employeededuction where empCode = '" + empCode + "' and deductionID = '" + deductionID + "' and deductionType = '" + deductionType.Text + "'";
MySqlCommand com = new MySqlCommand(insert, con);
MySqlCommand com1 = new MySqlCommand(checkDeduction, con);
MySqlDataReader rd;
DataTable tab = new DataTable();
try
{
con.Open();
rd = com1.ExecuteReader();
tab.Load(rd);
rd.Close();
if (tab.Rows.Count > 0)
{
MessageBox.Show("This deduction arleady exist for this employee");
}
else
{
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Added deduction " + deductionCombo.Text + " of amount " + deductionAmountTxt.Text + " to employee " + empCode);
deductionAmountTxt.Text = "";
ded = true;
all = false;
successTimer.Start();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("EmpCode or deductionID or Empty amount or DeductionType Error ");
}
}
}
}
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();
}
}
// update deduction for an employee
private void DedUpdateBtn_Click(object sender, EventArgs e)
{
if (dedID != null && deductionAmountTxt.Text != "" && deductionAmountTxt.Visible == true && deductionType.Text != "" && deductionCombo.Text != "")
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string update = "update employeededuction set amountDeducted = '" + deductionAmountTxt.Text +
"', deductiontype = '" + deductionType.Text +
"', dateForDeduction = '" + deductionDate.Text + "' where ID = '" + dedID + "'";
MySqlCommand com = new MySqlCommand(update, con);
MySqlDataReader rd;
try
{
con.Open();
//update
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed " + deductionCombo.Text + " Deduction amount from " + deductionamount + " to " + deductionAmountTxt.Text + " for Employee " + EmployeeFullName + " of employeeID " + empCode + "");
MessageBox.Show("Update Successful!");
deductionCombo.Items.Clear();
GetDetails(EmployeeFullName);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else if (dedID != null && percentageTxt.Text != "" && percentageTxt.Visible == true && deductionCombo.Text != "" && deductionType.Text != "")
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string update = "update employeededuction set percentage = '" + percentageTxt.Text +
"', deductiontype = '" + deductionType.Text +
"', dateForDeduction = '" + deductionDate.Text + "' where ID = '" + dedID + "'";
MySqlCommand com = new MySqlCommand(update, con);
MySqlDataReader rd;
try
{
con.Open();
//update
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed " + deductionCombo.Text + " Deduction percentage from " + percentage + " to " + percentageTxt.Text + " for Employee " + EmployeeFullName + " of employeeID " + empCode + "");
MessageBox.Show("Update Successful!");
deductionCombo.Items.Clear();
GetDetails(EmployeeFullName);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please select the name from the list and select deduction from the list to perform this operation", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void updateEmpBtn_Click(object sender, EventArgs e)
{
if (firstNameTxt.Text == "" ||
lastNameTxt.Text == "" ||
empCodeTxt.Text == "" ||
salaryCategory.selectedValue == "" ||
scaleBaseTxt.Text == "" ||
scalePercentTxt.Text == "" ||
emailTxt.Text == "" ||
phoneNumberTxt.Text == "" ||
positionTxt.Text == "" ||
bankNameTxt.Text == "" ||
bankAcountNumberTxt.Text == "" ||
employeeStatus.SelectedIndex == 0 ||
empCode == null ||
deductTitheOnCombo.SelectedIndex == 0 ||
departmentCombo.SelectedIndex == -1)
{
MessageBox.Show("Please, fill all the fields..!");
}
else if (empID == null)
{
MessageBox.Show("Please, select an Employee for updating.!");
}
else
{
switch (emailAllowed)
{
case true: //the email is correct, register employee
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string updateEmployee = "SET foreign_key_checks = 0;" +
" update employee" +
" JOIN employeeallowance ON employeeallowance.empCode = employee.empCode " +
" JOIN employeededuction ON employeededuction.empCode = employee.empCode " +
" set fname = '" + firstNameTxt.Text.ToUpper() +
"', mname = '" + middleNameTxt.Text.ToUpper() +
"', lname = '" + lastNameTxt.Text.ToUpper() +
"', employee.empCode = '" + empCodeTxt.Text.ToUpper() +
"', employeeallowance.empCode = '" + empCodeTxt.Text.ToUpper() +
"', employeededuction.empCode = '" + empCodeTxt.Text.ToUpper() +
"', DeptCode = '" + deptCode +
"', DOB = '" + dateOfBirth.Text +
"', salaryCategory = '" + salaryCategory.selectedValue +
"', scaleBase = '" + scaleBaseTxt.Text +
"', scalePercent = '" + scalePercentTxt.Text +
"', employeeEmail = '" + emailTxt.Text +
"', phoneNumber = '" + phoneNumberTxt.Text +
"', position = '" + positionTxt.Text.ToUpper() +
"', bankAccount = '" + bankAcountNumberTxt.Text.ToUpper() +
"', bankname = '" + bankNameTxt.Text.ToUpper() +
"', dateRegistered = '" + employedDate.Text +
"', employee.statuse = '" + employeeStatus.Text.ToUpper() +
"', deductTitheOn = '" + deductTitheOnCombo.Text + "' where employee.empID = '" + empID + "';" +
" SET foreign_key_checks = 1;";
MySqlCommand com = new MySqlCommand(updateEmployee, con);
MySqlDataReader rd;
try
{
con.Open();
//registering an employee
rd = com.ExecuteReader();
rd.Close();
Login.RecordUserActivity("Changed " + empname + " who had " + empCode + " as employee ID to " + empCodeTxt.Text);
clearFields();
LoadEmployee();
empID = null;
MessageBox.Show("Employee Updated Successful");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
break;
case false:
//the email is not correct
MessageBox.Show("Please, enter the valid email.");
break;
}
}
}
//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");
}
}