public PayrollData GetEmployeePayroll(int employeeId, string storeId, DateTime startDate, DateTime finishDate, string fullName)
{
PayrollData payrollData = new PayrollData();
ConnectToDatabase();
string query = "SELECT * FROM tblWorkHours " +
"JOIN tblEmployeeStore ON tblEmployeeStore.EmployeeId = tblWorkHours.EmployeeId " +
"WHERE tblEmployeeStore.StoreId = @storeId AND tblWorkHours.StoreId = @storeId AND tblWorkHours.EmployeeId = @employeeId AND Date >= @startDate AND Date <= @finishDate";
MySqlCommand cmd = new MySqlCommand(query, mConn);
cmd.Parameters.Add(new MySqlParameter("startDate", startDate));
cmd.Parameters.Add(new MySqlParameter("finishDate", finishDate));
cmd.Parameters.Add(new MySqlParameter("employeeId", employeeId));
cmd.Parameters.Add(new MySqlParameter("storeId", storeId));
MySqlDataReader rdr = cmd.ExecuteReader();
double totalWorkHours = 0;
double totalRotaHours = 0;
int row = 0;
string errorMessage = "";
while (rdr.Read())
{
string startTime = rdr["StartTime"].ToString();
string finishTime = rdr["FinishTime"].ToString();
TimeSpan workHours = TimeSpan.Parse(finishTime).Subtract(TimeSpan.Parse(startTime));
totalWorkHours += workHours.Subtract(TimeSpan.Parse(rdr["BreakTime"].ToString())).TotalHours;
//check for errors in the work hours which need to be displayed to the manager
if (TimeSpan.Parse(startTime) == new TimeSpan(0, 0, 0) ^ TimeSpan.Parse(finishTime) == new TimeSpan(0, 0, 0))
{
errorMessage += string.Format("A start time or finish time hasn't been entered on date: {0} for employee: {1} \n\n", DateTime.Parse(rdr["Date"].ToString()).ToShortDateString(), fullName);
}
else if (TimeSpan.Parse(startTime) > TimeSpan.Parse(finishTime))
{
errorMessage += string.Format("The finish time is before the start time on date: {0} for employee: {1} \n\n", DateTime.Parse(rdr["Date"].ToString()).ToShortDateString(), fullName);
}
string rotaStartTime = rdr["RotaStartTime"].ToString();
string rotaFinishTime = rdr["RotaFinishTime"].ToString();
TimeSpan rotaWorkHours = TimeSpan.Parse(rotaFinishTime).Subtract(TimeSpan.Parse(rotaStartTime));
totalRotaHours += rotaWorkHours.Subtract(TimeSpan.Parse(rdr["BreakTime"].ToString())).TotalHours;
row++;
}
payrollData.HoursRota = Math.Round(totalRotaHours, 2);
payrollData.HoursWorked = Math.Round(totalWorkHours, 2);
payrollData.ErrorMessage = errorMessage;
rdr.Close();
CloseConnection();
return(payrollData);
}
public PayrollData GetEmployeePayroll(int week, Year year)
{
List <DateTime> wholeWeek = year.GetWeekStartFinish(week);
DateTime startWeek = wholeWeek[0];
DateTime finishWeek = wholeWeek[1];
string fullName = mFirstName + " " + mLastName;
PayrollData payrollData = mDBConnection.GetEmployeePayroll(mId, mStoreId, startWeek, finishWeek, fullName);
payrollData.EmployeeName = fullName;
payrollData.ContractHours = mContractHours;
if (payrollData.HoursWorked < payrollData.ContractHours)
{
payrollData.HolidayHours = Math.Round(payrollData.ContractHours - payrollData.HoursWorked, 2);
}
else
{
payrollData.HolidayHours = 0;
}
return(payrollData);
}
