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