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