public List <EmpHours> ListEmployeeHours(int empID)
{
List <EmpHours> EmpHList = new List <EmpHours>();
//2.Create, setup, and open a connection object
SqlConnection conn = new SqlConnection();
conn.ConnectionString = DataSource;
conn.Open();
//3.Create a command object
SqlCommand cmd = new SqlCommand("sp_select_EmpHoursByID", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmpID", empID));
//4.Execute the command
SqlDataReader reader = cmd.ExecuteReader();
//5.Handle the results
while (reader.Read())
{
EmpHours empH = new EmpHours();
empH.EmployeeID = Convert.ToInt32(reader["EmpID"]);
empH.EmployeeWorkDate = Convert.ToDateTime(reader["WorkDate"]);
empH.EmployeeHours = (float)Convert.ToDouble(reader["Hours"]);
EmpHList.Add(empH);
}
//6. Close the connection
conn.Close();
//return array
return(EmpHList);
}
public static string Checker(EmpHours empHours)
{
string output = "";
// Method to pass validation inputs to correct methods
if (DateFormat(empHours.Date.ToString()) == true && (NumbersOnly(empHours.Hours) == true))
{
// Check that hours is actually a number
try
{
double doubleCheck = double.Parse(empHours.Hours.ToString()) * 2;
}
catch (Exception)
{
return("Invalid Hours (nonRegexCheck)!");
}
return("Pass");
}
else if (DateFormat(empHours.Date.ToString()) == false)
{
//return "Invalid Date Format!\nDD/MM/YYYY or D/MM/YYYY only";
output += "Invalid Date Format!\nDD/MM/YYYY or D/MM/YYYY only!\n";
}
if (NumbersOnly(empHours.Hours.ToString()) == false)
{
//return "Invalid Hours!";
output += "Invalid Hours!\n";
}
else
{
return("unknown failure");
}
return(output);
}
public static int InsertHours(EmpHours empHours)
{
// Format date so we can split it using (null) [whitespaces]
//string date = emp.Date.Replace("/", " ");
//date = date.Replace("-", " ");
//string[] dateFormat = date.Split(null);
//date = dateFormat[2] + dateFormat[1] + dateFormat[0];
// Open the SQL connection
myConnection.Open();
// Create a new SQL command using the stored procedure
SqlCommand add = new SqlCommand("dbo.sp_EmpHours_InsertEmployeeHours", myConnection);
// Set commandtype to store procedure
add.CommandType = CommandType.StoredProcedure;
// Add parameters to command
add.Parameters.AddWithValue("@empID", empHours.Emp.EmpID);
add.Parameters.AddWithValue("@workDate", empHours.Date);
add.Parameters.AddWithValue("@hours", empHours.Hours);
// Execture store procedure
add.ExecuteNonQuery();
// Close the connection (VERY important!)
myConnection.Close();
return((int)SuccessEnum.Success);
}
//method - Display employee hours and name
public List <EmpHours> DisplayHours(int empid)
{
List <EmpHours> list = new List <EmpHours>();
//connect
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConnectionHelper.GetConnectionString();
conn.Open();
using (conn)
{
//command
SqlCommand command = new SqlCommand("sp_EmpHours_DisplayWorkinfo", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@empid", empid));
//run
SqlDataReader reader = command.ExecuteReader();
//loop to map each row
while (reader.Read())
{
//convert row into objects
EmpHours emp = new EmpHours();
emp.EmpID = Convert.ToInt32(reader["EmpID"]);
emp.WorkDate = Convert.ToDateTime(reader["WorkDate"]);
emp.Hours = Convert.ToDecimal(reader["Hours"]);
list.Add(emp);
}
return(list);
}
}
public void CreateWork(EmpHours aEmpHour)
{
//step 1: create connection the object
SqlConnection conn = new SqlConnection();
//step 2: assign the connection string
conn.ConnectionString = "Data Source=PEA104-18;Initial Catalog=EmpDatabase;User ID=sa;Password=Petersham";
//step 3: open the connection
conn.Open();
//step 4 - create command object
SqlCommand comm = new SqlCommand("sp_EmpHours_Insert", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
//add parameters
comm.Parameters.Add(new SqlParameter("@empID", aEmpHour.EmpID));
comm.Parameters.Add(new SqlParameter("@workDate", aEmpHour.WorkDate));
comm.Parameters.Add(new SqlParameter("@hours", aEmpHour.Hours));
//step 5 - run the command
comm.ExecuteNonQuery();
//step 6 - close connection
conn.Close();
}
//Record employee hours
#region
private void btnRecordHours_Click(object sender, EventArgs e)
{
//validation
if (!ValidationHelper.NumberWithDashOrDecimal(txtHours.Text))
{
MessageBox.Show("Please enter the amount of hours employee has worked.");
return;
}
//read input
EmpHours empHours = new EmpHours();
empHours.EmpID = int.Parse(txtIdEmp.Text);
empHours.WorkDate = DateTime.Parse(dtbWorkDate.Text);
empHours.Hours = decimal.Parse(txtHours.Text);
//pass to controller
EmpHoursController controller = new EmpHoursController();
ResultEnum result = controller.AddHours(empHours);
//show output
switch (result)
{
case ResultEnum.Success:
MessageBox.Show("Hours added.");
break;
case ResultEnum.Fail:
MessageBox.Show("Cannot add hours");
break;
}
}
// Add Date and Hours to Employee timesheet
public static int InsertHours(EmpHours emp)
{
try
{
return(EmployeeDao.InsertHours(emp));
}
catch (Exception)
{
return((int)SuccessEnum.Fail);
}
}
//method add employee hours
public ResultEnum AddHours(EmpHours emp)
{
try
{
dao.AddHours(emp);
return(ResultEnum.Success);
}
catch (Exception ex)
{
//logging
Console.WriteLine("Error - EmpHoursDao/AddHours - " + ex.Message);
return(ResultEnum.Fail);
}
}
public StateEnum SaveEmployeeHours(EmpHours emp)
{
StateEnum result = StateEnum.Success;
try
{
dao.InsertHours(emp);
}
catch
{
result = StateEnum.Fail;
}
return(result);
}
private void btnUpdateHours_Click(object sender, EventArgs e)
{
string output = "";
DialogResult result = MessageBox.Show("Do you want update employee hours?", "Update hours?", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
// Read input
emp.EmpID = int.Parse(lblUpdateEmpIDText.Text);
// Create new EmpHours object
EmpHours empHours = new EmpHours(emp);
empHours.Date = Convert.ToDateTime(txtUpdateWorkDate.Text);
empHours.Hours = txtUpdateHours.Text;
// Call to DataHandler, then off to RegexCheck and finally StoredProcedure
if (RegexCheck.Checker(empHours) == "Pass")
{
// Add employee to DB and store the result
int success = DataHandler.InsertHours(empHours);
// Display the result
if (success == (int)SuccessEnum.Success)
{
output += "Added Employee Hours to the DataBase!\n";
}
else
{
output += "Failed to add Employee Hours to the DataBase!\n";
}
}
// Regex fail message
else
{
output += RegexCheck.Checker(empHours) + "\n";
}
MessageBox.Show(output, "Result");
txtUpdateHours.Text = "";
txtUpdateWorkDate.Text = monthCalendar.TodayDate.ToShortDateString();
}
}
//method - add employee work hours
public void AddHours(EmpHours emp)
{
//connect
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConnectionHelper.GetConnectionString();
conn.Open();
using (conn)
{
//command - run sql
SqlCommand command = new SqlCommand("sp_EmpHours_Insert", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@empid", emp.EmpID));
command.Parameters.Add(new SqlParameter("@workdate", emp.WorkDate));
command.Parameters.Add(new SqlParameter("@hours", emp.Hours));
//run
command.ExecuteNonQuery();
}
}
private void btnRecordHours_Click(object sender, EventArgs e)
{
//read the input
EmpHours emp = new EmpHours();
if (lbliD.Text != "")
{
try
{
emp.EmployeeID = int.Parse(lbliD.Text);
emp.EmployeeWorkDate = dtpWorkDate.Value;
emp.EmployeeHours = float.Parse(txtHours.Text);
//call the service
StateEnum result = service.SaveEmployeeHours(emp);
//check the result
switch (result)
{
case StateEnum.Fail:
MessageBox.Show("Cannot save employee hours");
break;
case StateEnum.Success:
MessageBox.Show("Employee hours added");
break;
}
}
catch
{
MessageBox.Show("Please use a number");
}
try { RefreshHours(int.Parse(lbliD.Text)); } catch { }
}
else
{
MessageBox.Show("Please select an employee in the list on the left first");
errorProvider1.SetError(lbliD, "No employee Id has been set yet");
}
}
// Get EmpHours by EmpID
public static List <EmpHours> GetEmpHours(Employee emp)
{
List <EmpHours> empHoursList = new List <EmpHours>();
// Open the SQL connection
myConnection.Open();
// Create a new SQL command using the stored procedure
SqlCommand get = new SqlCommand("dbo.sp_EmpHours_GetEmpHours", myConnection);
// Set commandtype to store procedure
get.CommandType = CommandType.StoredProcedure;
// Add parameters to command
get.Parameters.AddWithValue("@empID", emp.EmpID);
// Execture store procedure
SqlDataReader myReader = get.ExecuteReader();
// Check myReader has hours info
if (myReader.HasRows)
{
while (myReader.Read())
{
// Create new EmpHours
EmpHours empHours = new EmpHours(emp);
empHours.EmpHoursID = (int)(myReader["EmpHoursID"]);
empHours.Emp.EmpID = (int)(myReader["EmpID"]);
empHours.Date = (DateTime)(myReader["WorkDate"]);
empHours.Hours = (myReader["Hours"]).ToString();
// Add to List
empHoursList.Add(empHours);
}
}
// Close the connection (VERY important!)
myConnection.Close();
return(empHoursList);
}
public void InsertHours(EmpHours empHours)
{
//2.Create, setup, and open a connection object
SqlConnection conn = new SqlConnection();
conn.ConnectionString = DataSource;
conn.Open();
//3.Create a command object
SqlCommand cmd = new SqlCommand("sp_insert_EmpHours", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmpID", empHours.EmployeeID));
cmd.Parameters.Add(new SqlParameter("@WorkDate", empHours.EmployeeWorkDate));
cmd.Parameters.Add(new SqlParameter("@Hours", empHours.EmployeeHours));
//4.Execute the command
cmd.ExecuteNonQuery();
//5.Handle the results
//6. Close the connection
conn.Close();
}
public static int InsertHours(EmpHours emp)
{
// Add a new Employee Working Hours and Date to the DataBase
// Call StoredProcedure
return(EmployeeService.InsertHours(emp));
}
