/* Decline button for the completed hours gridview
*/
protected void DeclineButton(object sender, System.EventArgs e)
{ /*
* //Get the button that raised the event
* LinkButton btn = (LinkButton)sender;
*
* //Get the row that contains this button
* GridViewRow gvr = (GridViewRow)btn.NamingContainer;*/
//Grid row number
int num = gvr.RowIndex;
int WeekOfMonth = GetWeekOfMonth.GetWeekNumberOfMonth(Convert.ToDateTime(gvr.Cells[2].Text));
string[] name;
string firstName;
string lastName;
DateTime dt = Convert.ToDateTime(gvr.Cells[1].Text);
DateTime dt1 = Convert.ToDateTime(gvr.Cells[2].Text);
name = gvr.Cells[0].Text.Split(' ');
firstName = name[0];
lastName = name[1];
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
con.Open();
string CompletedHours = ("DELETE FROM Calendar WHERE Id = @CurrentUser " +
"and FacilitatorFirstName = @FirstName and FacilitatorLastName = @LastName " +
"and StartTime = @StartTime and EndTime = @EndTime and RoomId = @Room");
SqlCommand GetCompletedHours = new SqlCommand(CompletedHours, con);
GetCompletedHours.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
GetCompletedHours.Parameters.AddWithValue("@FirstName", firstName);
GetCompletedHours.Parameters.AddWithValue("@LastName", lastName);
GetCompletedHours.Parameters.AddWithValue("@StartTime", dt);
GetCompletedHours.Parameters.AddWithValue("@EndTime", dt1);
GetCompletedHours.Parameters.AddWithValue("@Room", GetRoomId(gvr.Cells[3].Text));
SqlDataReader addHoursReader = GetCompletedHours.ExecuteReader();
Response.Redirect("index.aspx");
}
/*Submits the hours to stats with the donated hours going to the updated email
* **Code mostly repeated from Confirm button to be refactored**
*/
protected void onConfirm(object sender, EventArgs e)
{
//GridViewRow gvr = GridView4.SelectedRow;
/*
* //Get the button that raised the event
* LinkButton btn = (LinkButton)sender;
*
* //Get the row that contains this button
* GridViewRow gvr = (GridViewRow)btn.NamingContainer; */
//Grid row number
int num = gvr.RowIndex;
int WeekOfMonth = GetWeekOfMonth.GetWeekNumberOfMonth(Convert.ToDateTime(gvr.Cells[2].Text));
var manager = Context.GetOwinContext().GetUserManager <ApplicationUserManager>();
string[] name;
string firstName;
string lastName;
string newFirstName;
string newLastName;
string startTime;
string endTime;
string[] startDate;
string[] endDate;
string[] array;
string month;
string year;
DateTime dt = Convert.ToDateTime(gvr.Cells[1].Text);
DateTime dt1 = Convert.ToDateTime(gvr.Cells[2].Text);
float totalHours = (float)(dt1 - dt).TotalHours;
//Gets the selected facilitator name from the dropdown
name = FacilitatorDropDown.Text.Split(' ');
newFirstName = name[0];
newLastName = name[1];
//Gets the original facilitator so that the proper row in Calendar dataTable gets deleted
name = gvr.Cells[0].Text.Split(' ');
firstName = name[0];
lastName = name[1];
startDate = gvr.Cells[1].Text.Split(' ');
startTime = startDate[1];
endDate = gvr.Cells[2].Text.Split(' ');
endTime = endDate[1];
//fixes the case where time is split on "-" or "/"
if (endDate[0].Contains('-'))
{
array = endDate[0].Split('-');
month = array[1];
year = array[0];
}
else
{
array = endDate[0].Split('/');
month = array[0];
if (array[2].Length == 2)
{
year = "20" + array[2];
}
else
{
year = array[2];
}
}
//Checks if the timeslot is the lunch hour and gives double time if it is
if ((TimeSpan.Compare(dt.TimeOfDay, new TimeSpan(12, 0, 0)) == 0 ||
TimeSpan.Compare(dt.TimeOfDay, new TimeSpan(12, 0, 0)) == -1) &&
(TimeSpan.Compare(dt1.TimeOfDay, new TimeSpan(13, 0, 0)) == 0 ||
TimeSpan.Compare(dt1.TimeOfDay, new TimeSpan(13, 0, 0)) == 1))
{
//Look for Field Trips (Only double time for lunch on regular days)
SqlConnection fieldTripCheck = new SqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()
};
SqlDataAdapter adapter = new SqlDataAdapter();
fieldTripCheck.Open();
SqlDataAdapter getFieldTrips = new SqlDataAdapter("Select Location From FieldTrips where CONVERT(DATE, StartTime) = @CurrentDate",
ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
getFieldTrips.SelectCommand.Parameters.AddWithValue("@CurrentDate", dt.Date);
DataTable table = new DataTable();
getFieldTrips.Fill(table);
//If not a fieldtrip give double time for lunch hour
if (table.Rows.Count == 0)
{
totalHours += 1;
}
fieldTripCheck.Close();
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
con.Open();
string CompletedHours = ("INSERT INTO Stats (Id, FacilitatorFirstName, FacilitatorLastName, RoomId, " +
"WeekOfMonth, WeekOfYear, Month, Year, WeeklyHours, Donate) VALUES (@NewUser, @NewFirstName, " +
"@NewLastName, @Room, @WeekOfMonth, @WeekOfYear, @Month, @Year, @WeeklyHours, @CurrentUser); " +
"DELETE FROM Calendar WHERE Id = @CurrentUser and FacilitatorFirstName = @FirstName and FacilitatorLastName = @LastName " +
"and StartTime = @StartTime and EndTime = @EndTime and RoomId = @Room");
SqlCommand GetCompletedHours = new SqlCommand(CompletedHours, con);
//The userID is gotte
GetCompletedHours.Parameters.AddWithValue("@NewUser", EmailTextbox.Text);
GetCompletedHours.Parameters.AddWithValue("@NewFirstName", newFirstName);
GetCompletedHours.Parameters.AddWithValue("@NewLastName", newLastName);
GetCompletedHours.Parameters.AddWithValue("@WeekOfMonth", WeekOfMonth);
GetCompletedHours.Parameters.AddWithValue("@WeekOfYear", GetWeekOfMonth.GetWeekOfYear(DateTime.Parse(gvr.Cells[2].Text)));
GetCompletedHours.Parameters.AddWithValue("@Month", month);
GetCompletedHours.Parameters.AddWithValue("@Year", year);
GetCompletedHours.Parameters.AddWithValue("@WeeklyHours", totalHours);
GetCompletedHours.Parameters.AddWithValue("@StartTime", dt);
GetCompletedHours.Parameters.AddWithValue("@EndTime", dt1);
GetCompletedHours.Parameters.AddWithValue("@Room", GetRoomId(gvr.Cells[3].Text));
GetCompletedHours.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
GetCompletedHours.Parameters.AddWithValue("@FirstName", firstName);
GetCompletedHours.Parameters.AddWithValue("@LastName", lastName);
SqlDataReader addHoursReader = GetCompletedHours.ExecuteReader();
manager.SendEmail(EmailTextbox.Text, "Facilitation Hours Recieved", newFirstName + " " + newLastName + " has recieved " + totalHours + " hours from " +
User.Identity.GetUserId() + " (" + firstName + " " + lastName + ")" + " for the month of " + CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Int32.Parse(month)) + "!");
Response.Redirect(Request.RawUrl);
}
protected void Page_Load(object sender, EventArgs e)
{
WeeklyHoursLabel.ForeColor = System.Drawing.Color.Red;
DateTime startDate;
//startdate would be grabbed from database
startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 13, 45, 6);
//Open Connection
SqlConnection con = new SqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()
};
SqlDataAdapter adapter = new SqlDataAdapter();
con.Open();
//Upcoming Hours
string upc = "SELECT (F.FacilitatorFirstName + ' '+ F.FacilitatorLastName) AS FacilitatorName, F.StartTime, F.EndTime, R.Room FROM dbo.Calendar AS F, Rooms as R WHERE " +
"F.Id = @CurrentUser and F.EndTime > @CurrentTime and F.RoomId = R.RoomId";
SqlCommand getup = new SqlCommand(upc, con);
getup.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
getup.Parameters.AddWithValue("@CurrentTime", DateTime.Now);
adapter.SelectCommand = new SqlCommand(upc, con);
//Execture the querey
SqlDataReader upcompQuery = getup.ExecuteReader();
GridView3.DataSource = upcompQuery;
GridView3.DataBind();
if (!upcompQuery.HasRows)
{
spacer.Controls.Add(new LiteralControl("<br />"));
Label1.Visible = true;
}
upcompQuery.Close();
//Completed Hours
string comp = "SELECT (F.FacilitatorFirstName + ' '+ F.FacilitatorLastName) AS FacilitatorName, F.StartTime, " +
"F.EndTime, R.Room FROM dbo.Calendar AS F, Rooms as R WHERE " +
"F.Id = @CurrentUser and F.EndTime < @CurrentTime and R.RoomId = F.RoomId";
SqlCommand getComp = new SqlCommand(comp, con);
getComp.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
getComp.Parameters.AddWithValue("@CurrentTime", DateTime.Now);
adapter.SelectCommand = new SqlCommand(comp, con);
//Execture the querey
SqlDataReader completedQuery = getComp.ExecuteReader();
//Assign results
GridView4.DataSource = completedQuery;
//Bind the data
GridView4.DataBind();
if (!completedQuery.HasRows)
{
spacer.Controls.Add(new LiteralControl("<br />"));
Label4.Visible = true;
}
completedQuery.Close();
//Get Facilitators
string Facilitators = "SELECT (F.FirstName + ' '+ F.LastName) AS FacilitatorName FROM dbo.Facilitators AS F WHERE " +
"F.Id = @CurrentUser";
SqlCommand getFacilitators = new SqlCommand(Facilitators, con);
getFacilitators.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
adapter.SelectCommand = new SqlCommand(Facilitators, con);
//Execture the querey
SqlDataReader facilitatorReader = getFacilitators.ExecuteReader();
//Assign results
GridView1.DataSource = facilitatorReader;
//Bind the data
GridView1.DataBind();
facilitatorReader.Close();
//Get children
string Children = "SELECT (C.FirstName + ' '+ C.LastName) AS Name, C.Grade as Grade, C.Class as Classroom FROM dbo.Children AS C WHERE " +
"C.Id = @CurrentUser";
SqlCommand getChildren = new SqlCommand(Children, con);
getChildren.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
SqlDataReader childReader = getChildren.ExecuteReader();
//Assign results
GridView2.DataSource = childReader;
//Bind the data
GridView2.DataBind();
childReader.Close();
/*Label1.Visible = true;
* Label1.Text = GetWeekOfMonth.GetWeekOfYear(new DateTime(2018, 2,28)).ToString();*/
//Get number of children
string numKids = "SELECT COUNT(*) FROM Children Where Id = @CurrentUser";
SqlCommand getNumKids = new SqlCommand(numKids, con);
getNumKids.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
int count = (int)getNumKids.ExecuteScalar();
//Get Weekly Hours
string WeeklyHours = "SELECT SUM(S.WeeklyHours) as WeeklyHours FROM dbo.Stats AS S WHERE S.Id = @CurrentUser AND S.WeekOfYear = @WeekOfYear " +
" and S.Year = @Year GROUP BY S.Id";
SqlCommand getWeeklyHours = new SqlCommand(WeeklyHours, con);
getWeeklyHours.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
getWeeklyHours.Parameters.AddWithValue("@WeekOfYear", GetWeekOfMonth.GetWeekOfYear(DateTime.Now));
getWeeklyHours.Parameters.AddWithValue("@Year", DateTime.Now.Year);
SqlDataReader WeeklyHoursReader = getWeeklyHours.ExecuteReader();
if (WeeklyHoursReader.Read())
{
WeeklyHoursLabel.Text = WeeklyHoursReader["WeeklyHours"].ToString();
float weeklyTotal = float.Parse(WeeklyHoursLabel.Text);
if (weeklyTotal >= 2.5 && count < 2)
{
WeeklyHoursLabel.ForeColor = System.Drawing.Color.Green;
}
if (weeklyTotal >= 5 && count >= 2)
{
WeeklyHoursLabel.ForeColor = System.Drawing.Color.Green;
}
}
WeeklyHoursReader.Close();
//Get Monthly Hours
string MonthlyHours = "(SELECT SUM(S.WeeklyHours) AS MonthlyHours FROM dbo.Stats AS S WHERE S.Id = @CurrentUser AND S.Month = @Month " +
"AND S.Year = @Year GROUP BY S.Id)";
SqlCommand getMonthlyHours = new SqlCommand(MonthlyHours, con);
getMonthlyHours.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
getMonthlyHours.Parameters.AddWithValue("@Month", DateTime.Now.Month);
getMonthlyHours.Parameters.AddWithValue("@Year", DateTime.Now.Year);
SqlDataReader MonthlyHoursReader = getMonthlyHours.ExecuteReader();
if (MonthlyHoursReader.Read())
{
MonthlyHoursLabel.Text = MonthlyHoursReader["MonthlyHours"].ToString();
}
MonthlyHoursReader.Close();
con.Close();
}
//Confirm button on completed hours grid
protected void ConfirmButton(object sender, System.EventArgs e)
{
//Grid row number
int num = gvr.RowIndex;
int WeekOfMonth = GetWeekOfMonth.GetWeekNumberOfMonth(Convert.ToDateTime(gvr.Cells[2].Text));
string[] name;
string firstName;
string lastName;
string startTime;
string endTime;
string[] startDate;
string[] endDate;
string[] array;
string month;
string year;
string day;
int hour;
int min;
DateTime calendarStart = Convert.ToDateTime(gvr.Cells[1].Text);
DateTime calendarEnd = Convert.ToDateTime(gvr.Cells[2].Text);
name = gvr.Cells[0].Text.Split(' ');
firstName = name[0];
lastName = name[1];
startDate = gvr.Cells[1].Text.Split(' ');
startTime = startTimeTextBox.Text;
endDate = gvr.Cells[2].Text.Split(' ');
endTime = endTimeTextBox.Text;
//fixes the case where time is split on "-" or "/"
if (endDate[0].Contains('-'))
{
array = endDate[0].Split('-');
month = array[1];
year = array[0];
day = array[2];
}
else
{
array = endDate[0].Split('/');
month = array[0];
day = array[1];
if (array[2].Length == 2)
{
year = "20" + array[2];
}
else
{
year = array[2];
}
}
array = startTimeTextBox.Text.Split(':');
hour = Convert.ToInt32(array[0]);
min = Convert.ToInt32(array[1]);
DateTime dt = new DateTime(Convert.ToInt32(year), Convert.ToInt32(month), Convert.ToInt32(day), hour, min, 0);
System.Diagnostics.Debug.WriteLine(dt.ToString());
array = endTimeTextBox.Text.Split(':');
hour = Convert.ToInt32(array[0]);
min = Convert.ToInt32(array[1]);
DateTime dt1 = new DateTime(Convert.ToInt32(year), Convert.ToInt32(month), Convert.ToInt32(day), hour, min, 0);
System.Diagnostics.Debug.WriteLine(dt1.ToString());
float totalHours = (float)(dt1 - dt).TotalHours;
//Checks if the timeslot is the lunch hour and gives double time if it is
if ((TimeSpan.Compare(dt.TimeOfDay, new TimeSpan(12, 0, 0)) == 0 ||
TimeSpan.Compare(dt.TimeOfDay, new TimeSpan(12, 0, 0)) == -1) &&
(TimeSpan.Compare(dt1.TimeOfDay, new TimeSpan(13, 0, 0)) == 0 ||
TimeSpan.Compare(dt1.TimeOfDay, new TimeSpan(13, 0, 0)) == 1))
{
//Look for Field Trips (Only double time for lunch on regular days)
SqlConnection fieldTripCheck = new SqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()
};
SqlDataAdapter adapter = new SqlDataAdapter();
fieldTripCheck.Open();
SqlDataAdapter getFieldTrips = new SqlDataAdapter("Select Location From FieldTrips where CONVERT(DATE, StartTime) = @CurrentDate",
ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
getFieldTrips.SelectCommand.Parameters.AddWithValue("@CurrentDate", dt.Date);
DataTable table = new DataTable();
getFieldTrips.Fill(table);
//If not a fieldtrip give double time for lunch hour
if (table.Rows.Count == 0)
{
totalHours += 1;
}
fieldTripCheck.Close();
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
con.Open();
string CompletedHours = ("INSERT INTO Stats (Id, FacilitatorFirstName, FacilitatorLastName, RoomId, " +
"WeekOfMonth, WeekOfYear, Month, Year, WeeklyHours, Donate) VALUES (@CurrentUser, @FirstName, " +
"@LastName, @Room, @WeekOfMonth, @WeekOfYear, @Month, @Year, @WeeklyHours, @Donate); " +
"DELETE FROM Calendar WHERE Id = @CurrentUser and FacilitatorFirstName = @FirstName and FacilitatorLastName = @LastName " +
"and StartTime = @StartTime and EndTime = @EndTime and RoomId = @Room");
SqlCommand GetCompletedHours = new SqlCommand(CompletedHours, con);
GetCompletedHours.Parameters.AddWithValue("@CurrentUser", User.Identity.GetUserId());
GetCompletedHours.Parameters.AddWithValue("@FirstName", firstName);
GetCompletedHours.Parameters.AddWithValue("@LastName", lastName);
GetCompletedHours.Parameters.AddWithValue("@WeekOfMonth", WeekOfMonth);
GetCompletedHours.Parameters.AddWithValue("@WeekOfYear", GetWeekOfMonth.GetWeekOfYear(DateTime.Parse(gvr.Cells[2].Text)));
GetCompletedHours.Parameters.AddWithValue("@Month", month);
GetCompletedHours.Parameters.AddWithValue("@Year", year);
GetCompletedHours.Parameters.AddWithValue("@WeeklyHours", totalHours);
GetCompletedHours.Parameters.AddWithValue("@StartTime", calendarStart);
GetCompletedHours.Parameters.AddWithValue("@EndTime", calendarEnd);
GetCompletedHours.Parameters.AddWithValue("@Donate", "");
GetCompletedHours.Parameters.AddWithValue("@Room", GetRoomId(gvr.Cells[3].Text));
SqlDataReader addHoursReader = GetCompletedHours.ExecuteReader();
//Page_Load(null, EventArgs.Empty);
Response.Redirect(Request.RawUrl);
}
//Binds the TotalStatsGridView **DOES NOT WORK WHEN AN ABSENCE EXTENDS INTO A NEW CALENDAR YEAR***
protected void BindTotalStats(string month, string year, string ID)
{
//Open Connection
SqlConnection con = new SqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()
};
con.Open();
//Gets the number of kids the family has
string countKids = "SELECT COUNT(*) FROM Children Where Id = @User";
SqlCommand getNumKids = new SqlCommand(countKids, con);
getNumKids.Parameters.AddWithValue("@User", ID);
int numKids = (int)getNumKids.ExecuteScalar();
//Gets the total absent days the facilitator is missing for the year
SqlDataAdapter adapter = new SqlDataAdapter();
string absences = "SELECT StartDate, EndDate FROM Absence where Email = @User AND YEAR(StartDate) " +
"= @Year AND Confirmed = 1";
SqlCommand getAbsences = new SqlCommand(absences, con);
adapter.SelectCommand = new SqlCommand(absences, con);
getAbsences.Parameters.AddWithValue("@Year", year);
getAbsences.Parameters.AddWithValue("@User", ID);
SqlDataReader reader = getAbsences.ExecuteReader();
Double yearAabsentDays = 0;
//Gets the total weeks so far in the year (based on the selected dropdown date)
Double yearTotalWeeks = GetWeekOfMonth.GetWeekOfYear(new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)));
//((new DateTime(Int32.Parse(year), Int32.Parse(month), DateTime.DaysInMonth(Int32.Parse(year), Int32.Parse(month))) - new DateTime(Int32.Parse(year), 1, 1)).TotalDays) / 7;
Double totalYearlyHours;
//Subtracts the absent days from the total days
while (reader.Read())
{
DateTime start = (DateTime)reader.GetValue(0);
DateTime end = (DateTime)reader.GetValue(1);
yearAabsentDays += (end - start).Days;
}
yearTotalWeeks -= (yearAabsentDays / 7);
reader.Close();
//Gets the total absent days the facilitator is missing for the Month
SqlDataAdapter monthAdapter = new SqlDataAdapter();
string monthAbsences = "SELECT StartDate, EndDate FROM Absence where Email = @User AND YEAR(StartDate) " +
"= @Year AND MONTH(StartDate) = @Month AND Confirmed = 1";
SqlCommand getMonthAbsences = new SqlCommand(monthAbsences, con);
monthAdapter.SelectCommand = new SqlCommand(monthAbsences, con);
getMonthAbsences.Parameters.AddWithValue("@Year", year);
getMonthAbsences.Parameters.AddWithValue("@Month", month);
getMonthAbsences.Parameters.AddWithValue("@User", ID);
SqlDataReader monthReader = getMonthAbsences.ExecuteReader();
Double monthAbsentDays = 0;
//Gets the total weeks so far in the year (based on the selected dropdown date)
Double monthTotalWeeks = GetWeekOfMonth.MondaysInMonth(new DateTime(Int32.Parse(year), Int32.Parse(month), 1));
Double totalMonthlyHours;
//Subtracts the absent days from the total days
while (monthReader.Read())
{
DateTime start = (DateTime)monthReader.GetValue(0);
DateTime end = (DateTime)monthReader.GetValue(1);
monthAbsentDays += (end - start).Days;
}
monthTotalWeeks -= (monthAbsentDays / 7);
monthReader.Close();
//Gets how many hours the facilitator needs to work for the year
switch (numKids)
{
case 0:
totalYearlyHours = 0;
totalMonthlyHours = 0;
break;
case 1:
totalYearlyHours = yearTotalWeeks * 2.5;
totalMonthlyHours = monthTotalWeeks * 2.5;
break;
default:
totalYearlyHours = yearTotalWeeks * 5;
totalMonthlyHours = monthTotalWeeks * 5;
break;
}
//Rounds to the nearest hour
totalYearlyHours = Math.Round(totalYearlyHours, 1);
totalMonthlyHours = Math.Round(totalMonthlyHours, 2);
//Gets the total hours the family has worked this year
DataTable dt = new DataTable();
dt.Columns.Add("MonthlyTotal", typeof(string));
dt.Columns.Add("YearlyTotal", typeof(string));
DataRow dr = dt.NewRow();
dr["MonthlyTotal"] = (Double.Parse(GetMonthlyHours(month, year, ID)) - totalMonthlyHours).ToString();
dr["YearlyTotal"] = (Double.Parse(GetYearlyHours(month, year, ID)) - totalYearlyHours).ToString();
dt.Rows.Add(dr);
TotalStatsGridView.DataSource = dt;
TotalStatsGridView.DataBind();
con.Close();
}
//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();
}
