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