protected List <TimeTableRow> GenerateHoursTable(DateTime startDate, DateTime endDate, String UserID)
{
//read shifts from the shift table
webtimeclockEntities db = new webtimeclockEntities();
//query the table for shifts with the matching user id and within specified begin and end time
var table = from row in db.shifts
where row.UserID == UserID
where row.Date >= startDate
where row.Date <= endDate
orderby row.Date
select row;
//create time table rows with the selected shifts
List <TimeTableRow> timeTableRows = new List <TimeTableRow>();
foreach (shift row in table)
{
//read data from data table
DateTime shiftDate = row.Date.Date;
string timeIn = row.TimeIn.ToShortTimeString();
string timeOut = row.TimeOut.ToShortTimeString();
TimeSpan timeWorked = row.TimeWorked;
string totalHoursWorked = timeWorked.Hours + ":" + timeWorked.Minutes + ":" + timeWorked.Seconds;
TimeSpan rounded = row.RoundedTimeWorked;
string roundedHoursWorked = rounded.Hours + ":" + rounded.Minutes;
string comment = row.Comments;
//add rows to time table
TimeTableRow timeTableRowToAdd = new TimeTableRow(shiftDate.ToShortDateString(), timeIn, timeOut, totalHoursWorked, roundedHoursWorked, comment);
timeTableRows.Add(timeTableRowToAdd);
}
return(timeTableRows);
}
public void GetTimeTable(List <TimeTableRow> rows)
{
string query = @"SELECT Courses.CourseName, Guides.FirstName, Guides.LastName, WorkingDays.Name, WorkingHours.HourName, Rooms.RoomName
FROM((((((TimeTable INNER JOIN
WorkingDays ON TimeTable.[Day] = WorkingDays.DayCode) INNER JOIN
WorkingHours ON TimeTable.[Hour] = WorkingHours.HourCode) INNER JOIN
GuidesInCourses ON TimeTable.GuideCourseCode = GuidesInCourses.GuideCourseCode) INNER JOIN
Courses ON GuidesInCourses.CourseCode = Courses.CourseCode) INNER JOIN
Rooms ON TimeTable.RoomCode = Rooms.RoomCode) INNER JOIN
Guides ON GuidesInCourses.GuideCode = Guides.GuideCode)";
try
{
OleDbDataReader reader = GetReader(query);
while (reader.Read())
{
TimeTableRow row = new TimeTableRow(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
rows.Add(row);
}
// Always call Close when done reading.
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
