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;
}
