/// <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(); }