//Databinds RoomHoursGridView private void BindRoomHours(string month, string year, string ID) { int firstWeekOfMonth = GetWeekOfMonth.FirstMonday(Convert.ToInt32(month)); //Open Connection SqlConnection con = new SqlConnection { ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString() }; con.Open(); //Gets each week, the month and the year for each facilitator string upc = "SELECT Rooms.Room, Week1.Week1, Week2.Week2, Week3.Week3, " + "Week4.Week4, Monthly.MonthTotal, Yearly.YearTotal " + "FROM(SELECT R.Room, sum(WeeklyHours) as MonthTotal " + "FROM Stats as S, Rooms as R WHERE R.RoomId = S.RoomId AND " + "S.Month = @Month AND S.Id = @ID GROUP BY R.Room) AS Monthly " + "FULL JOIN " + "(SELECT R.Room, sum(WeeklyHours) as YearTotal " + "FROM Stats as S, Rooms as R WHERE R.RoomId = S.RoomId AND " + "S.Year = @Year AND S.Id = @ID GROUP BY R.Room) AS Yearly " + "ON Yearly.Room = Monthly.Room " + "FULL JOIN( " + "(SELECT SUM(S.WeeklyHours) AS Week1, R.Room " + "FROM dbo.Stats AS S, Rooms AS R WHERE R.RoomId = S.RoomId AND S.Id = @ID AND S.Month = @MONTH " + "AND S.Year = @YEAR AND S.WeekOfYear = @Week1 GROUP BY R.Room)) " + "AS Week1 " + "ON Yearly.Room = Week1.Room " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week2, R.Room " + "FROM dbo.Stats AS S, Rooms AS R WHERE R.RoomId = S.RoomId AND S.Id = @ID AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week2 GROUP BY R.Room) AS Week2 " + "ON Week2.Room = Yearly.Room " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week3, R.Room " + "FROM dbo.Stats AS S, Rooms AS R WHERE R.RoomId = S.RoomId AND S.Id = @ID AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week3 GROUP BY R.Room) " + "AS Week3 " + "ON Week3.Room = Yearly.Room " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week4, R.Room " + "FROM dbo.Stats AS S, Rooms AS R WHERE R.RoomId = S.RoomId AND S.Id = @ID AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week4 GROUP BY R.Room) AS Week4 " + "ON Week4.Room = Yearly.Room " + "RIGHT JOIN " + "(SELECT Room FROM Rooms) AS Rooms " + "ON Rooms.Room = Yearly.Room"; SqlCommand getup = new SqlCommand(upc, con); getup.Parameters.AddWithValue("@Week1", firstWeekOfMonth); getup.Parameters.AddWithValue("@Week2", firstWeekOfMonth + 1); getup.Parameters.AddWithValue("@Week3", firstWeekOfMonth + 2); getup.Parameters.AddWithValue("@Week4", firstWeekOfMonth + 3); getup.Parameters.AddWithValue("@Month", month); getup.Parameters.AddWithValue("@Year", year); getup.Parameters.AddWithValue("@ID", ID); //Execture the querey SqlDataReader upcompQuery = getup.ExecuteReader(); RoomHoursGridView.DataSource = upcompQuery; RoomHoursGridView.DataBind(); upcompQuery.Close(); con.Close(); }
//Databinds FacilitatorHoursGridView private void BindFacilitatorHours(string month, string year, string ID) { int firstWeekOfMonth = GetWeekOfMonth.FirstMonday(Convert.ToInt32(month)); //Open Connection SqlConnection con = new SqlConnection { ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString() }; con.Open(); //Gets each week, the month and the year for each facilitator string upc = "SELECT Facilitators.Name, Week1.Week1, Week2.Week2, Week3.Week3, " + "Week4.Week4, Monthly.MonthTotal, Yearly.YearTotal " + "FROM(SELECT(S.FacilitatorFirstName + ' ' + S.FacilitatorLastName) AS Name, sum(WeeklyHours) as MonthTotal " + "FROM Stats as S WHERE S.Month = @Month GROUP BY(S.FacilitatorFirstName + ' ' + S.FacilitatorLastName)) AS Monthly " + "FULL JOIN " + "(SELECT (S.FacilitatorFirstName +' ' + S.FacilitatorLastName) AS Name, sum(WeeklyHours) as YearTotal " + "FROM Stats as S WHERE S.Year = @Year GROUP BY(S.FacilitatorFirstName +' ' + S.FacilitatorLastName)) AS Yearly " + "ON Monthly.Name = Yearly.Name " + "FULL JOIN( " + "(SELECT SUM(S.WeeklyHours) AS Week1, (S.FacilitatorFirstName +' ' + S.FacilitatorLastName) AS Name " + "FROM dbo.Stats AS S WHERE S.Id = @User AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week1 GROUP BY(S.FacilitatorFirstName +' ' + S.FacilitatorLastName)) " + ") AS Week1 " + "ON Week1.Name = Yearly.Name " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week2, (S.FacilitatorFirstName +' ' + S.FacilitatorLastName) AS Name " + "FROM dbo.Stats AS S WHERE S.Id = @User AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week2 GROUP BY(S.FacilitatorFirstName +' ' + S.FacilitatorLastName)) " + "AS Week2 " + "ON Week2.Name = Yearly.Name " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week3, (S.FacilitatorFirstName +' ' + S.FacilitatorLastName) AS Name " + "FROM dbo.Stats AS S WHERE S.Id = @User AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week3 GROUP BY(S.FacilitatorFirstName +' ' + S.FacilitatorLastName)) " + "AS Week3 " + "ON Week3.Name = Yearly.Name " + "FULL JOIN " + "(SELECT SUM(S.WeeklyHours) AS Week4, (S.FacilitatorFirstName +' ' + S.FacilitatorLastName) AS Name " + "FROM dbo.Stats AS S WHERE S.Id = @User AND S.Month = @Month " + "AND S.Year = @Year AND S.WeekOfYear = @Week4 GROUP BY(S.FacilitatorFirstName +' ' + S.FacilitatorLastName)) " + "AS Week4 " + "ON Week4.Name = Yearly.Name " + "RIGHT JOIN " + "(SELECT (F.FirstName +' ' + F.LastName) AS Name FROM dbo.Facilitators AS F WHERE F.Id = @User) AS Facilitators " + "ON Facilitators.Name = Yearly.Name"; SqlCommand getup = new SqlCommand(upc, con); getup.Parameters.AddWithValue("@Week1", firstWeekOfMonth); getup.Parameters.AddWithValue("@Week2", firstWeekOfMonth + 1); getup.Parameters.AddWithValue("@Week3", firstWeekOfMonth + 2); getup.Parameters.AddWithValue("@Week4", firstWeekOfMonth + 3); getup.Parameters.AddWithValue("@Month", month); getup.Parameters.AddWithValue("@Year", year); getup.Parameters.AddWithValue("@User", ID); //Execture the querey SqlDataReader upcompQuery = getup.ExecuteReader(); FacilitatorHoursGridView.DataSource = upcompQuery; FacilitatorHoursGridView.DataBind(); upcompQuery.Close(); con.Close(); }