public List <EmpHour> SelectHoursByEmpId(int empId) { connection = ConnectionHelper.GetConnection(); using (connection) { SqlCommand cmd = new SqlCommand("sp_EmpHours_SelectHoursByEmpId", connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@empId", empId)); SqlDataReader reader = cmd.ExecuteReader(); List <EmpHour> list = new List <EmpHour>(); while (reader.Read()) { EmpHour empHour = new EmpHour(); empHour.EmpHourID = Convert.ToInt32(reader["EmpHourID"]); empHour.EmpID = Convert.ToInt32(reader["EmpID"]); empHour.WorkDate = Convert.ToDateTime(reader["WorkDate"]); empHour.Hours = Convert.ToInt32(reader["Hours"]); list.Add(empHour); } return(list); } }
private void cmbWorkDate_SelectedIndexChanged(object sender, EventArgs e) { EmpHour empH = (EmpHour)cmbWorkDate.SelectedItem; EmpHourManager ehM = new EmpHourManager(); Result <List <EmpHour> > resultEH = ehM.GetEmpWorkByEmpHourId(empH.EmpHourID); switch (resultEH.Status) { case ResultsEnum.SUCCESS: List <EmpHour> .Enumerator eList = resultEH.List.GetEnumerator(); while (eList.MoveNext()) { EmpHour empH1 = eList.Current; if (empH1.EmpHourID == empH.EmpHourID) { txtHours.Text = empH1.Hour.ToString(); break; } } break; case ResultsEnum.FAIL: MessageBox.Show("Fail to get the employee working hour list"); break; } }
private void btnSearchWorkHour_Click(object sender, EventArgs e) { EmpHour empH = new EmpHour(); EmpHourManager eM = new EmpHourManager(); List <EmpHour> workList = eM.GetEmpWork(); }
//method - select date and hours by id public List <EmpHour> FindEmpHourById(int id) { List <EmpHour> list = new List <EmpHour>(); //create connection SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Data Source=.;Initial Catalog=Yi_PD2_ETSDatabase;Integrated Security=True"; conn.Open(); using (conn) { //#2 - create command SqlCommand command = new SqlCommand("sp_EmpHours_SelectWorkDateAndHoursByID", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("EmpID", SqlDbType.Int); command.Parameters["EmpID"].Value = id; //#3 - run command SqlDataReader reader = command.ExecuteReader(); //#4 - handle results while (reader.Read()) { //map record to object EmpHour emp = new EmpHour(); emp.EmpHoursID = Convert.ToInt32(reader["EmpHoursID"]); emp.WorkDate = Convert.ToDateTime(reader["WorkDate"]); emp.Hours = Convert.ToDouble(reader["Hours"]); //add to list list.Add(emp); } return(list); } }
public void Insert(EmpHour empHrs) { //MAKE SURE SPELLING IS CORRECT within "" //STEP 2: Create, setup and open Connection object conn = new SqlConnection(); //Setup conn.ConnectionString = @"Data Source=.;Initial Catalog=ETS_Database_1.0;Integrated Security=True"; //open conn.Open(); //start.Connect(); // STEP 3: Create Command Obj SqlCommand cmd = new SqlCommand("sp_Insert_Employee_Hours", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@EmpID", empHrs.EmpID)); cmd.Parameters.Add(new SqlParameter("@Date", empHrs.WorkDate)); cmd.Parameters.Add(new SqlParameter("@Hours", empHrs.HoursWorked)); // STEP 4: Execure the Cmd cmd.ExecuteNonQuery(); // STEP 5: Handle Results // STEP 6: Close Connection conn.Close(); //start.Disconnect(); }
//method - select work hour by date; public List <EmpHour> SelectEmpHoursByDate(DateTime date) { List <EmpHour> list = new List <EmpHour>(); //#1 - create connection SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Data Source=.;Initial Catalog=Yi_PD2_ETSDatabase;Integrated Security=True"; conn.Open(); using (conn) { //#2 - create command SqlCommand command = new SqlCommand("sp_EmpHours_SelectByDate", conn); command.CommandType = CommandType.StoredProcedure; //add parameters command.Parameters.Add("@WorkDate", SqlDbType.Date); command.Parameters["@WorkDate"].Value = date; //#3 - run command SqlDataReader reader = command.ExecuteReader(); //#4 - handle results while (reader.Read()) { //map record to object EmpHour eHour = new EmpHour(); eHour.EmpHoursID = Convert.ToInt32(reader["EmpHoursID"]); eHour.EmpID = Convert.ToInt32(reader["EmpID"]); eHour.WorkDate = Convert.ToDateTime(reader["WorkDate"]); eHour.Hours = Convert.ToDouble(reader["Hours"]); list.Add(eHour); } return(list); } }
private void btnLogHrs_Click(object sender, EventArgs e) { int EmpID = int.Parse(txtEmpID.Text); DateTime WorkDate = dtpWorkDate.Value; int HoursWorked = int.Parse(txtHours.Text); EmpHour eh = new EmpHour(EmpID, WorkDate, HoursWorked); // call HoursLogged HoursLogged hl = new HoursLogged(); Status result = hl.HrsLogged(eh); // message to say saved or not switch (result) { case Status.Successfull: MessageBox.Show($"Hours logged Successfully"); break; case Status.Unsuccessfull: MessageBox.Show($"Hours NOT logged!"); break; } StatusData <List <EmpHour> > List = hl.EmpHoursList(); lvEmpHours.Items.Clear(); foreach (EmpHour emp in List.Data) { lvEmpHours.Items.Add(new ListViewItem(new string[] { emp.EmpID.ToString(), $"{emp.FName} {emp.LName}", emp.WorkDate.ToString(), emp.HoursWorked.ToString() })); } }
public List <EmpHour> SelectEmpHourByWorkDate(DateTime workDate) { List <EmpHour> empList = new List <EmpHour>(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = DataHelper.GetConnecctionString(); conn.Open(); using (conn) { SqlCommand comm = new SqlCommand("sp_TblEmpHour_SelectDate", conn); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.Add(new SqlParameter("@workDate", workDate)); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { EmpHour empH = new EmpHour(); empH.EmpID = Convert.ToInt32(reader["EmpID"]); empH.EmpHourID = Convert.ToInt32(reader["EmpHourID"]); empH.WorkDate = Convert.ToDateTime(reader["WorkDate"]); empH.Hour = Convert.ToDouble(reader["Hour"]); empH.FullName = Convert.ToString(reader["FullName"]); empList.Add(empH); } return(empList); } }
public List <EmpHour> SelectAllWorkEmp() { List <EmpHour> list = new List <EmpHour>(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = DataHelper.GetConnecctionString(); conn.Open(); using (conn) { SqlCommand comm = new SqlCommand("sp_TblEmpHour_DisplayAllWorkEmpID", conn); comm.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { EmpHour empH = new EmpHour(); empH.EmpID = Convert.ToInt32(reader["EmpID"]); empH.FullName = Convert.ToString(reader["FullName"]); list.Add(empH); } return(list); } }
private void btnRecordHours_Click(object sender, EventArgs e) { try { EmpHour empH = new EmpHour(); empH.WorkDate = DateTime.Parse(dtpWorkDate.Text); empH.Hour = double.Parse(txtHours.Text); empH.EmpID = (int)cmbEmp.SelectedValue; Validation v = new Validation(); v.ValidateDate(empH.WorkDate); v.ValidateHour(empH.Hour); EmpHourManager empHManager = new EmpHourManager(); ResultsEnum result = empHManager.AddWork(empH); Employee emp = (Employee)cmbEmp.SelectedItem; string empFullName = emp.FirstName + " " + emp.LastName; switch (result) { case ResultsEnum.SUCCESS: MessageBox.Show("Employee ID " + emp.EmpID + "\n" + empFullName + "\nWork Date: " + empH.WorkDate.ToShortDateString() + "\nHour: " + decimal.Round((decimal)empH.Hour, 2) + "\n\nWork Added"); break; case ResultsEnum.FAIL: MessageBox.Show("Sorry... please try again later"); break; } } catch (FormatException) { MessageBox.Show("Invalid input"); } catch (EmptyInputException) { MessageBox.Show("Input cannot be empty"); } catch (WhiteSpaceException) { MessageBox.Show("Input cannot contain space"); } catch (FutureException) { MessageBox.Show("Invalid work date"); } catch (NoWorkHourException) { MessageBox.Show("Work hour must greater than zero"); } catch (MaxDailyWorkException) { MessageBox.Show("No more than 24 hours work in a day"); } }
private void btnSaveChanges_Click(object sender, EventArgs e) { EmpHour empH = (EmpHour)cmbWorkDate.SelectedItem; try { EmpHour empH1 = (EmpHour)lstEmp.SelectedItem; empH1.WorkDate = empH.WorkDate; empH1.Hour = double.Parse(txtHours.Text); empH1.EmpHourID = empH.EmpHourID; Validation v = new Validation(); v.ValidateDate(empH1.WorkDate); v.ValidateHour(empH1.Hour); EmpHourManager empHManager = new EmpHourManager(); ResultsEnum result = empHManager.UpdateWorkHour(empH1); switch (result) { case ResultsEnum.SUCCESS: MessageBox.Show("Work Hour updated"); break; case ResultsEnum.FAIL: MessageBox.Show("Sorry...please try again later"); break; } } catch (FormatException) { MessageBox.Show("Invalid input"); } catch (EmptyInputException) { MessageBox.Show("Input cannot be empty"); } catch (WhiteSpaceException) { MessageBox.Show("Input cannot contain space"); } catch (FutureException) { MessageBox.Show("Invalid date"); } catch (NoWorkHourException) { MessageBox.Show("Work hour must greater than zero"); } catch (MaxDailyWorkException) { MessageBox.Show("No more than 24 hours work in a day"); } }
public void HrsLoggedTest_InvalidHoursLoged() { // Arrange HoursLogged hl = new HoursLogged(); EmpHour hrs = null; Status expectedResult = Status.Unsuccessfull; // Act Status actualResult = hl.HrsLogged(hrs); // Assert Assert.AreEqual(expectedResult, actualResult); }
// Method to return Logged status public Status HrsLogged(EmpHour hrs) { Status result = Status.Successfull; try { dao.Insert(hrs); } catch { result = Status.Unsuccessfull; } return(result); }
//methods public void InsertEmpHour(EmpHour empHour) { connection = ConnectionHelper.GetConnection(); using (connection) { SqlCommand cmd = new SqlCommand("sp_EmpHours_InsertHours", connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@empId", empHour.EmpID)); cmd.Parameters.Add(new SqlParameter("@workdate", empHour.WorkDate)); cmd.Parameters.Add(new SqlParameter("@hours", empHour.Hours)); cmd.ExecuteNonQuery(); } }
//methods public ResultEnum InsertEmployeeHour(EmpHour empHour) { ResultEnum result = ResultEnum.Success; try { dao.InsertEmpHour(empHour); } catch (Exception e) { result = ResultEnum.Fail; Console.WriteLine("Exception occured " + e.Message); } return(result); }
public void UpdateEmpHour(EmpHour empH) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = DataHelper.GetConnecctionString(); conn.Open(); using (conn) { SqlCommand comm = new SqlCommand("sp_TblEmpHour_UpdateWorkHour", conn); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.Add(new SqlParameter("@empHourId", empH.EmpHourID)); comm.Parameters.Add(new SqlParameter("@workDate", empH.WorkDate)); comm.Parameters.Add(new SqlParameter("@hour", empH.Hour)); comm.ExecuteNonQuery(); } }
public void CreateWork(EmpHour aEmpHour) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = DataHelper.GetConnecctionString(); conn.Open(); using (conn) { SqlCommand comm = new SqlCommand("sp_TblEmpHour_Insert", conn); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.Add(new SqlParameter("@empID", aEmpHour.EmpID)); comm.Parameters.Add(new SqlParameter("@workDate", aEmpHour.WorkDate)); comm.Parameters.Add(new SqlParameter("@hours", aEmpHour.Hour)); comm.ExecuteNonQuery(); } }
public List <EmpHour> EmpIDHours(int EmpID) //find by Emp obj method { List <EmpHour> EmpList = new List <EmpHour>(); //MAKE SURE SPELLING IS CORRECT within "" //STEP 2: Create, setup and open Connection object conn = new SqlConnection(); //Setup conn.ConnectionString = @"Data Source=.;Initial Catalog=ETS_Database_1.0;Integrated Security=True"; //open conn.Open(); //create //start.Connect(); // STEP 3: Create Command Obj SqlCommand cmd = new SqlCommand("sp_Select_EmpHoursByEmpID", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@EmpID", EmpID)); // STEP 4: Execure the Cmd SqlDataReader read = cmd.ExecuteReader(); // STEP 5: Handle Results while (read.Read()) { string FName = Convert.ToString(read["FirstName"]); string LName = Convert.ToString(read["LastName"]); int empID = Convert.ToInt32(read["EmpID"]); DateTime WorkDate = Convert.ToDateTime(read["WorkDate"]); int Hours = Convert.ToInt32(read["Hours"]); EmpHour AllEmp = new EmpHour(empID, FName, LName, WorkDate, Hours); EmpList.Add(AllEmp); } // STEP 6: Close Connection conn.Close(); //start.Disconnect(); // return the Emp OBJ return(EmpList); }
private void lstEmp_SelectedIndexChanged(object sender, EventArgs e) { Employee emp = (Employee)lstEmp.SelectedItem; lblEmpId.Text = "Employee ID: " + emp.EmpID; EmpHourManager ehM = new EmpHourManager(); Result <List <EmpHour> > resultEH = ehM.GetEmpWorkByEmpId(emp.EmpID); double totalHour = 0; switch (resultEH.Status) { case ResultsEnum.SUCCESS: lstWorkDate.DataSource = lstWorkHour.DataSource = resultEH.List; lstWorkDate.DisplayMember = "WorkDate"; lstWorkDate.ValueMember = "EmpId"; lstWorkHour.DisplayMember = "Hour"; lstWorkHour.ValueMember = "Hour"; List <EmpHour> .Enumerator eList = resultEH.List.GetEnumerator(); while (eList.MoveNext()) { EmpHour empH1 = eList.Current; if (empH1.EmpID == emp.EmpID) { totalHour += empH1.Hour; } } break; case ResultsEnum.FAIL: MessageBox.Show("Fail to get the work list"); break; } lblTotalHours.Text = "Total working hours:\n" + totalHour; }
private void lstEmp_SelectedIndexChanged(object sender, EventArgs e) { EmpHour empH = (EmpHour)lstEmp.SelectedItem; txtEmpId.Text = empH.EmpID.ToString(); EmpHourManager ehM = new EmpHourManager(); Result <List <EmpHour> > resultEH = ehM.GetEmpWorkByEmpId(empH.EmpID); switch (resultEH.Status) { case ResultsEnum.SUCCESS: cmbWorkDate.DataSource = resultEH.List; cmbWorkDate.DisplayMember = "WorkDate"; cmbWorkDate.ValueMember = "EmpHourID"; break; case ResultsEnum.FAIL: MessageBox.Show("Fail to get the work list"); break; } }
private void btnAddHours_Click(object sender, EventArgs e) { //input EmpHour hrs = new EmpHour(); hrs.EmpID = ((Employee)lstEmployees.SelectedItem).EmpID; hrs.WorkDate = DateTime.Parse(dtpWorkDate.Text); hrs.Hours = int.Parse(txtHours.Text); //process - add employee EmployeeHourManager manager = new EmployeeHourManager(); ResultEnum result = manager.InsertEmployeeHour(hrs); //output if (result == ResultEnum.Success) { MessageBox.Show("Hours added successfully"); } else { MessageBox.Show("Error occured!"); } }
private void lstWorkDate_SelectedIndexChanged(object sender, EventArgs e) { DateTime date = (DateTime)lstWorkDate.SelectedItem; EmpHourManager ehM = new EmpHourManager(); Result <List <EmpHour> > resultEH = ehM.GetAllEmpByDate(date); double totalHour = 0; switch (resultEH.Status) { case ResultsEnum.SUCCESS: lstEmpName.DataSource = lstEmpHour.DataSource = resultEH.List; lstEmpName.DisplayMember = "FullName"; lstEmpHour.DisplayMember = "Hour"; lstEmpName.ValueMember = lstEmpHour.ValueMember = "EmpID"; List <EmpHour> .Enumerator eList = resultEH.List.GetEnumerator(); while (eList.MoveNext()) { EmpHour empH1 = eList.Current; if (empH1.WorkDate == date) { totalHour += empH1.Hour; } } break; case ResultsEnum.FAIL: MessageBox.Show("Fail to get the employee list"); break; } lblDate.Text = "Date: " + date.ToShortDateString(); lblDailyTotalHour.Text = "Daily total hour:\n" + totalHour; }