/// <summary>
/// this function add the data to the datatable from database
/// </summary>
/// <param name="DBName">it Takes the Database Name</param>
/// <returns></returns>
public DataTable ShowData(string DBName)
{
string query = "Select * from " + DBName;
SqlDataAdapter sql = new SqlDataAdapter(query, DataBaseConnection.getInstance().getConnection());
DataTable table = new DataTable();
sql.Fill(table);
table.Columns.Remove("Id");
return(table);
}
/// <summary>
/// this function add all data to the database
/// </summary>
/// <param name="attendence">object of Attendence</param>
public static void MarkAttendence(Attendence attendence)
{
int AttendenceId = attendence.AttendenceId;
DateTime LoginTime = attendence.LoginTime;
DateTime LogoutTime = attendence.LogoutTime;
DateTime AttendenceDate = attendence.AttendenceDate;
string query = string.Format("Insert Into AttendenceRecord(AttendenceId, LogInTime, LogOutTime, AttendenceDate) Values('{0}', '{1}', '{2}', '{3}')", AttendenceId, LoginTime, LogoutTime, AttendenceDate);
DataBaseConnection.getInstance().executeQuery(query);
}
/// <summary>
/// This is the function used in event handler
/// </summary>
/// <returns></returns>
private DataTable showData()
{
string Id = txtId.Text.Substring(4, txtId.Text.Length - 4);
string query = "Select * from AttendenceRecord where AttendenceId = '" + Id + "'";
SqlDataAdapter cmd = new SqlDataAdapter(query, DataBaseConnection.getInstance().getConnection());
DataTable table = new DataTable();
cmd.Fill(table);
table.Columns.Remove("Id");
return(table);
}
/// <summary>
/// this function add employee data to the database
/// </summary>
/// <param name="employee"></param>
public void addEmployee(Employee employee)
{
string name = employee.Name;
string empId = employee.EmployeeId;
string cnic = employee.CNIC;
DateTime date = employee.AppointmentDate;
string salary = employee.MonthlySalary.ToString();
string email = employee.EmailId;
string address = employee.MailingAddress;
string query = String.Format("INSERT INTO EmployeeData(EmployeeId, Name, Cnic, AppointmentDate, Salary, Email, MailingAddress) values('{0}','{1}', '{2}', '{3}', '{4}', '{5}', '{6}' )", empId, name, cnic, date, salary, email, address);
int row = DataBaseConnection.getInstance().executeQuery(query);
}
/// <summary>
/// when button click it save the id and date
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSignIn_Click(object sender, EventArgs e)
{
int check = DataBaseConnection.getInstance().alreadyPresentInDB(txtId.Text, "EmployeeData", "EmployeeId");
if (check > 0)
{
id = Convert.ToInt32(txtId.Text.Substring(4, txtId.Text.Length - 4));
logInTime = DateTime.Now;
lblMessege.Text = "";
}
else
{
lblMessege.Text = "There is no employee of this ID";
}
}
/// <summary>
/// count the employee who marked the attendence
/// </summary>
/// <returns></returns>
private int countEmpAtt()
{
DateTime date = DateTime.Now.Date;
SqlDataReader reader = DataBaseConnection.getInstance().getData("Select * from AttendenceRecord");
int count = 0;
while (reader.Read())
{
if (date == DateTime.Parse(reader["AttendenceDate"].ToString()))
{
count += 1;
}
}
return(count);
}
/// <summary>
/// When event clicked it add the data to the datatable of database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLogOut_Click(object sender, EventArgs e)
{
Attendence attendence = new Attendence();
int check = DataBaseConnection.getInstance().alreadyPresentInDB(txtId.Text, "EmployeeData", "EmployeeId");
if (check > 0)
{
attendence.AttendenceId = id;
attendence.LoginTime = logInTime;
attendence.LogoutTime = DateTime.Now;
attendence.AttendenceDate = DateTime.Now.Date;
Employee.MarkAttendence(attendence);
dgvAttendenceRecord.DataSource = DataBaseConnection.getInstance().ShowData("AttendenceRecord");
showOnlyTime(1);
showOnlyTime(2);
lblMessege.Text = "";
}
else
{
lblMessege.Text = "There is no employee of this ID";
}
}
/// <summary>
/// This function get the total services of employee
/// </summary>
/// <param name="employee">string of employee</param>
/// <returns></returns>
public string getTotalServices(string employee)
{
string query = "Select * from EmployeeData where EmployeeId = '" + employee + "'";
SqlDataAdapter cmd = new SqlDataAdapter(query, DataBaseConnection.getInstance().getConnection());
DataTable table = new DataTable();
cmd.Fill(table);
string cell = table.Rows[0][4].ToString();
DateTime date = DateTime.Parse(cell);
var dt = DateTime.Now;
//Calculate the years between the dates
var years = new DateTime(DateTime.Now.Subtract(date).Ticks).Year - 1;
var pastYear = date.AddYears(years);
//Calculate the months between the dates
var months = 0;
for (int i = 1; i <= 12; i++)
{
if (pastYear.AddMonths(i) == dt)
{
months = i;
}
else if (pastYear.AddMonths(i) >= dt)
{
months = i - 1;
break;
}
}
//Calculate the days between the dates
var days = dt.Subtract(pastYear.AddMonths(months)).Days;
string d = string.Format("{0} years {1} months {2} days", years, months, days);
return(d);
}
/// <summary>
/// On load it shows the data in datagridview
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AddEmployee_Load(object sender, EventArgs e)
{
dgvEmployee.DataSource = Payroll.getInstance.ListOfEmployee();
lblEmployeeAdded.Text = "Total Employees Added: " + DataBaseConnection.getInstance().CountRows("EmployeeData");
txtId.Text = "Emp-" + countRows();
}
/// <summary>
/// Check that cnic is already present in database.
/// </summary>
/// <returns>return the integer value if greater than 1 it means cnic is already present</returns>
private int checkCnicInDB()
{
int checkCnic = DataBaseConnection.getInstance().alreadyPresentInDB(txtCnic.Text, "EmployeeData", "Cnic");
return(checkCnic);
}
/// <summary>
/// Check that Employee id is already present in database.
/// </summary>
/// <returns>return the integer value if greater than 1 it means Employeeid is already present</returns>
private int checkEmpIdInDB()
{
int checkEmpId = DataBaseConnection.getInstance().alreadyPresentInDB(txtId.Text, "EmployeeData", "EmployeeId");
return(checkEmpId);
}
/// <summary>
/// Check that email is already present in database.
/// </summary>
/// <returns>return the integer value if greater than 1 it means email is already present</returns>
private int checkEmailInDB()
{
int checkEmail = DataBaseConnection.getInstance().alreadyPresentInDB(txtEmail.Text, "EmployeeData", "Email");
return(checkEmail);
}
/// <summary>
/// close the connection when form is closed
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AttendenceSystem_FormClosed(object sender, FormClosedEventArgs e)
{
DataBaseConnection.getInstance().closeConnection();
}
/// <summary>
/// On load it shows the number of employee added
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void PayrollSysetem_Load(object sender, EventArgs e)
{
lblMarkAttendence.Text = countEmpAtt().ToString() + " Employees have Marked";
lblMarkAtt2.Text = "Attendence Today";
lblManageEmployees.Text = DataBaseConnection.getInstance().CountRows("EmployeeData") + " Employees have been Added";
}
/// <summary>
/// Count the number of rows in datatable of database
/// </summary>
/// <returns></returns>
private int countRows()
{
int count = DataBaseConnection.getInstance().CountRows("EmployeeData") + 1;
return(count);
}
/// <summary>
/// On load it shows the data in datagridview
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MarkAttendence_Load(object sender, EventArgs e)
{
dgvAttendenceRecord.DataSource = DataBaseConnection.getInstance().ShowData("AttendenceRecord");
showOnlyTime(1);
showOnlyTime(2);
}
public List <Employee> employeeWithShortAttendence(DateTime startDate, DateTime endDate)
{
int days = Convert.ToInt32((endDate.Date - startDate.Date).Days);
int totaldays = days;
for (int i = 0; i < days; i++)
{
if (startDate.DayOfWeek == DayOfWeek.Saturday || startDate.DayOfWeek == DayOfWeek.Sunday)
{
startDate = startDate.AddDays(1);
totaldays -= 1;
}
else
{
startDate = startDate.AddDays(1);
}
}
int workingHours = totaldays * 8;
List <Employee> list = new List <Employee>();
Employee employee = new Employee();
SqlDataAdapter cmd2 = new SqlDataAdapter("Select * from AttendenceRecord", DataBaseConnection.getInstance().getConnection());
SqlDataAdapter cmd1 = new SqlDataAdapter("Select EmployeeId from EmployeeData", DataBaseConnection.getInstance().getConnection());
DataTable table1 = new DataTable();
cmd1.Fill(table1);
DataTable table2 = new DataTable();
cmd2.Fill(table2);
int count = 0;
for (int j = 0; j < DataBaseConnection.getInstance().CountRows("EmployeeData"); j++)
{
for (int k = 0; k < DataBaseConnection.getInstance().CountRows("AttendenceRecord"); k++)
{
string id = "Emp-" + table2.Rows[k][table2.Columns.Count - 4].ToString();
if (table1.Rows[j].ToString() == id)
{
DateTime attDate = DateTime.Parse(table2.Rows[k][table2.Columns.Count - 1].ToString());
if (attDate >= startDate.Date && attDate <= endDate.Date)
{
DateTime logInTime = DateTime.Parse(table2.Rows[k][table2.Columns.Count - 3].ToString());
DateTime logOutTime = DateTime.Parse(table2.Rows[k][table2.Columns.Count - 2].ToString());
int hours = (logOutTime - logInTime).Hours;
count += hours;
}
}
}
if (count < workingHours && count > 0)
{
employee.EmployeeId = table1.Rows[j].ToString();
list.Add(employee);
}
}
return(list);
}
/// <summary>
/// this function contain the lists of employees
/// </summary>
/// <returns>return the table which contain the data</returns>
public DataTable ListOfEmployee()
{
DataTable table = DataBaseConnection.getInstance().ShowData("EmployeeData");
return(table);
}
/// <summary>
/// It Add Employee to the DataBase And do validations
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddEmployee_Click(object sender, EventArgs e)
{
txtId.Text = "Emp-" + countRows();
//This statement check that text boxes should not be empty
if (txtName.Text != "" && txtId.Text != "" && txtCnic.Text != "" && txtSalary.Text != null && txtEmail.Text != "" && txtMailingAddress.Text != "")
{
//This statement check the name is valid or not
if (Regex.IsMatch(txtName.Text, "^[a-zA-Z ]+$"))
{
//This statement check the cnic is of 13 numbers and contain only digits
if (txtCnic.Text.Length == 13 && txtCnic.Text.All(c => char.IsDigit(c)))
{
//This statement validate the email.
if (Regex.IsMatch(txtEmail.Text, @"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$"))
{
//Check employee id is already present in database or not
if (checkEmpIdInDB() > 0)
{
lblEmpIdMessege.Text = "Employee Id is already present";
}
//Check employee cnic is already present in database or not
else if (checkCnicInDB() > 0)
{
lblCnicMessege.Text = "CNIC is already present";
lblEmpIdMessege.Text = "";
}
//Check employee email is already present in database or not
else if (checkEmailInDB() > 0)
{
lblEmailMessege.Text = "Email is alredy present";
lblCnicMessege.Text = "";
}
//Add Data to the Database
else
{
addEmployee();
dgvEmployee.DataSource = Payroll.getInstance.ListOfEmployee();
lblEmployeeAdded.Text = "Total Employees Added: " + DataBaseConnection.getInstance().CountRows("EmployeeData");
}
}
else
{
lblEmailMessege.Text = "Invalid Email";
lblCnicMessege.Text = "";
}
}
else
{
lblCnicMessege.Text = "Invalid CNIC";
lblNameMessege.Text = "";
}
}
else
{
lblNameMessege.Text = "Invalid Name";
lblMessege.Text = "";
}
}
else
{
lblMessege.Text = "Information is Missing";
}
}
public PayrollSysetem()
{
//Connection string which connect to the database
DataBaseConnection.getInstance().ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\UsmanPC\Documents\EmployeeDB.mdf;Integrated Security=True;Connect Timeout=30";
InitializeComponent();
}
