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