//---Payment panel---------------------------- private void GetOutstandingFees() { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetOutstandingFees = new SqlCommand("SELECT OutstandingFees FROM tbl_outstandingFees WHERE StudentId = @studentId", conn); cmdGetOutstandingFees.Parameters.AddWithValue("@studentId", Session["s_studentId"]); try { lblOutstandingFees.Text = Convert.ToString(cmdGetOutstandingFees.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } }
//refresh role changes protected void btnRefreshRole_Click(object sender, EventArgs e) { string currentDate = DateTime.Now.ToShortDateString(); foreach (GridViewRow row in gvRevertRoleChange.Rows) { string date = row.Cells[6].Text.ToString(); string id = row.Cells[0].Text.ToString(); bool valid = IsDateBeforeOrToday(date); if (valid == false) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdRevertRoleChange = new SqlCommand("UPDATE tbl_user SET Role = @role Where Id = @id", conn); cmdRevertRoleChange.Parameters.AddWithValue("@role", Convert.ToInt32(row.Cells[1].Text)); cmdRevertRoleChange.Parameters.AddWithValue("@id", Convert.ToInt32(row.Cells[0].Text)); SqlCommand cmdDeleteChangeRecord = new SqlCommand("DELETE FROM tbl_roleChangeTemp WHERE Id = @id", conn); cmdDeleteChangeRecord.Parameters.AddWithValue("@id", Convert.ToInt32(row.Cells[7].Text)); try { cmdRevertRoleChange.ExecuteNonQuery(); cmdDeleteChangeRecord.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { lblRevertSuccess.Text = "Success, Roles Changes Has Been Reverted"; gvRevertRoleChange.DataBind(); gvUsers.DataBind(); } } } } }
protected void gvModules_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "btnAssignToModule") { var clickedButton = e.CommandSource as Button; var clickedRow = clickedButton.NamingContainer as GridViewRow; using (SqlConnection conn = Connections.ApplicationConnection()) { int academicId = Convert.ToInt32(Session["s_academicId"]); int moduleId = Convert.ToInt32(clickedRow.Cells[0].Text); string note = ""; //gets note TextBox txtNote = (TextBox)clickedRow.FindControl("txtNote"); note = txtNote.Text; //insert into table SqlCommand cmdInsertProgramManager = new SqlCommand("Insert INTO tbl_academicProgramManagerModule (AcademicProgramManagerId, ModuleId, Note) Values (@programManagerId, @moduleId, @note)", conn); cmdInsertProgramManager.Parameters.AddWithValue("@programManagerId", academicId); cmdInsertProgramManager.Parameters.AddWithValue("@moduleId", moduleId); cmdInsertProgramManager.Parameters.AddWithValue("@note", note); try { cmdInsertProgramManager.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); lblSuccessMessage.Text = "Success - You Have Assigned An Academic Program Manager To This Module !"; txtNote.Text = ""; } } } }
protected void Page_Load(object sender, EventArgs e) { if (Session["s_loggedUserId"] == null || Session["s_loggedUserRole"].ToString() != "20") { if (Session["s_loggedUserRole"].ToString() == "40") { } else if (Session["s_loggedUserRole"].ToString() == "30") { } else if (Session["s_loggedUserRole"].ToString() == "60") { } else { Response.Redirect("~/Default.aspx"); } } //number and name string number = Session["s_loggedNumber"].ToString(); string name; //ssql get name string sqlGetName = "SELECT CONCAT(Forename,' ',Surname) AS FullName From tbl_user WHERE Id=@id"; using (var cnn = Connections.ApplicationConnection()) { using (var cmdGetName = new SqlCommand(sqlGetName, cnn)) { cmdGetName.Parameters.AddWithValue("@id", Convert.ToInt32(Session["s_loggedUserId"])); name = Convert.ToString(cmdGetName.ExecuteScalar()); } } lblStaffNumber.Text = number + " - " + name; calDate.SelectedDate = Convert.ToDateTime(DateTime.Now.Date); calDate.VisibleDate = Convert.ToDateTime(DateTime.Now.ToString()); }
//gv students row command protected void gvStudents_RowCommand(object sender, GridViewCommandEventArgs e) { var clickedButton = e.CommandSource as Button; var clickedRow = clickedButton.NamingContainer as GridViewRow; if (e.CommandName == "cmdAddToTeam") { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdAddToTeam = new SqlCommand("INSERT INTO tbl_studentTeamMembers (TeamId, MemberId) Values(@teamId, @membersId)", conn); cmdAddToTeam.Parameters.AddWithValue("@teamId", Convert.ToInt32(Session["s_teamId"])); cmdAddToTeam.Parameters.AddWithValue("@membersId", Convert.ToInt32(clickedRow.Cells[0].Text)); try { cmdAddToTeam.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { string emailHeader = "Added To Team"; string emailSubject = "You Have Been Added To Team " + lblAddMembersTeamName.Text; string emailBody = "You have been added as a team member to the team " + lblAddMembersTeamName.Text + ". You can now send group emails and ceate events."; MailAddress mailAddress = new MailAddress(clickedRow.Cells[2].Text); string emailFileName = ""; string emailFile = ""; //send email Email.SendEmail(mailAddress, emailHeader, emailSubject, emailBody, emailFileName, emailFile); lblAddedToTeamSuccess.Text = " Success , Team Member Has Been Added ! "; conn.Close(); } } } }
protected void Page_Load(object sender, EventArgs e) { if (Session["s_loggedUserId"] == null || Session["s_loggedUserRole"].ToString() != "10") { Response.Redirect("~/Default.aspx"); } //get course title string courseTitle; string sqlGetTitle = "SELECT tbl_course.Name FROM tbl_course INNER JOIN tbl_studentCourse ON tbl_course.Id = tbl_studentCourse.CourseId WHERE tbl_studentCourse.StudentId = @studentId"; using (var cnn = Connections.ApplicationConnection()) { using (var cmdGetTitle = new SqlCommand(sqlGetTitle, cnn)) { cmdGetTitle.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); courseTitle = Convert.ToString(cmdGetTitle.ExecuteScalar()); } } //set course title lblCourse.Text = courseTitle; }
private void CheckDate() { DateTime startOfyear; using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetStartDate = new SqlCommand("SELECT TOP 1 StartDate FROM tbl_academicYearStart ORDER BY Id DESC", conn); try { startOfyear = Convert.ToDateTime(cmdGetStartDate.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } DateTime currentDate = Convert.ToDateTime(DateTime.Now.ToShortDateString()); DateTime topDate = Convert.ToDateTime(startOfyear.AddDays(14).ToShortDateString()); DateTime bottomDate = Convert.ToDateTime(startOfyear.AddDays(-14).ToShortDateString()); Debug.WriteLine("CURRENT DATE : " + currentDate); Debug.WriteLine("STAT DATE : " + startOfyear); Debug.WriteLine("TOP DATE : " + topDate); Debug.WriteLine("BOTTOM DATE : " + bottomDate); if (currentDate > bottomDate && currentDate < topDate) { Response.Redirect("~/Default.aspx"); } }
//----------------building---------------------------------------- protected void gvBuildings_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "cmdRemoveBuilding") { var clickedButton = e.CommandSource as Button; var clickedRow = clickedButton.NamingContainer as GridViewRow; using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdDeleteBuilding = new SqlCommand("DELETE FROM tbl_building WHERE Id = @id", conn); cmdDeleteBuilding.Parameters.AddWithValue("@id", Convert.ToInt32(clickedRow.Cells[0].Text)); SqlCommand cmdRemoveAlBuildings = new SqlCommand("DELETE FROM tbl_rooms WHERE BuildingId = @buildingId", conn); cmdRemoveAlBuildings.Parameters.AddWithValue("@buildingId", Convert.ToInt32(clickedRow.Cells[0].Text)); try { cmdDeleteBuilding.ExecuteNonQuery(); cmdRemoveAlBuildings.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); gvBuildings.DataBind(); ddlBuilding.DataBind(); ddlBuildingFilter.DataBind(); lblSuccess.Text = "Success, The Building Has Been Removed"; } } } }
//update event protected void btnUpdateEvent_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdUpdateEvent = new SqlCommand("UPDATE tbl_timeTable SET ClassTypeId = @classTypeId, Time = @time, DayId = @dayId, Location = @location WHERE Id = @eventId", conn); cmdUpdateEvent.Parameters.AddWithValue("@classTypeId", Convert.ToInt32(ddlEventType.SelectedValue)); cmdUpdateEvent.Parameters.AddWithValue("@time", txtTime.Text); cmdUpdateEvent.Parameters.AddWithValue("@dayId", Convert.ToInt32(ddlDay.SelectedValue)); cmdUpdateEvent.Parameters.AddWithValue("@location", txtLocation.Text); cmdUpdateEvent.Parameters.AddWithValue("@eventId", Convert.ToInt32(Session["s_eventId"])); cmdUpdateEvent.ExecuteNonQuery(); gvEvents.DataBind(); HideAllPanels(); lblSuccess.Text = "Success The Event Has Been Updates"; pnlEvents.Visible = true; } }
protected void btnUpdateStatus_Click(object sender, EventArgs e) { if (ddlStatus.SelectedItem.Text != "-- Select Status --") { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdUpdateStatus = new SqlCommand("UPDATE tbl_studentUser SET StatusId = @statusId WHERE StudentId = @studentId", conn); cmdUpdateStatus.Parameters.AddWithValue("@statusId", Convert.ToInt32(ddlStatus.SelectedValue)); cmdUpdateStatus.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_studentId"])); try { cmdUpdateStatus.ExecuteNonQuery(); lblError.CssClass = "text-success"; lblError.Text = "Success The Students Status Has Been Updated"; gvStudents.DataBind(); } catch (Exception ex) { throw ex; } finally { conn.Close(); hideAllPanels(); pnlStudentSearch.Visible = true; } } } else { lblError.Text = "Please Select A Status"; } }
protected void btnAddCourse_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdInsertCourse = new SqlCommand("INSERT INTO tbl_course (SchoolId, Name, Code) VALUES (@schoolId, @name, @code ) ", conn); cmdInsertCourse.Parameters.AddWithValue("@schoolId", Convert.ToInt32(ddlSchool.SelectedValue)); cmdInsertCourse.Parameters.AddWithValue("@name", txtName.Text.ToString()); cmdInsertCourse.Parameters.AddWithValue("@code", txtCode.Text.ToString()); try { if (ddlSchool.SelectedValue == "-- Select School --") { lblError.Text = "Please Slect A School"; } else { cmdInsertCourse.ExecuteNonQuery(); lblError.CssClass = "text-success"; lblError.Text = "Success Your Course Has Been Added"; } } catch (Exception ex) { throw ex; } finally { conn.Close(); ddlSchool.SelectedIndex = 0; txtName.Text = ""; txtCode.Text = ""; } } }
//add event protected void btnSubmitEvent_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdInsertEvent = new SqlCommand("INSERT INTO tbl_additionalEvents (Title,Description,Date,Time,UserId,Location) VALUES (@title,@description,@date,@time,@userId,@location)", conn); cmdInsertEvent.Parameters.AddWithValue("@title", txtTitle.Text); cmdInsertEvent.Parameters.AddWithValue("@description", txtDescription.Text); cmdInsertEvent.Parameters.AddWithValue("@date", Convert.ToDateTime(txtDate.Text)); cmdInsertEvent.Parameters.AddWithValue("@time", txtTime.Text); cmdInsertEvent.Parameters.AddWithValue("@userId", Convert.ToInt32(Session["s_loggedUserId"])); cmdInsertEvent.Parameters.AddWithValue("@location", txtLocation.Text); try { cmdInsertEvent.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { txtTitle.Text = ""; txtDescription.Text = ""; txtDate.Text = ""; txtTime.Text = ""; txtLocation.Text = ""; gvAdditionalEvents.DataBind(); pnlAddEvent.Visible = false; pnlAdditionalEvents.Visible = true; conn.Close(); } } }
protected void btnSubmitDate_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdInsertStartDate = new SqlCommand("INSERT INTO tbl_academicYearStart (StartDate) VALUES (@startDate)", conn); cmdInsertStartDate.Parameters.AddWithValue("@startDate", Convert.ToDateTime(txtStartDate.Text)); try { cmdInsertStartDate.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); txtStartDate.Text = ""; lblError.CssClass = "text-success"; lblError.Text = "Success The Start Date Has Been Set"; } } }
// ---------------------add user ---------------------- protected void btnAddUser_Click(object sender, EventArgs e) { //get last number from table using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetLastNumber = new SqlCommand("SELECT TOP 1 NUMBER FROM tbl_user ORDER BY Id Desc", conn); try { int lastNumber = Convert.ToInt32(cmdGetLastNumber.ExecuteScalar()); int newNumber = lastNumber + 1; txtNumber.Text = newNumber.ToString(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } HideAllPanels(); pnlAddUser.Visible = true; }
private void PopulateDropDowns() { using (SqlConnection conn = Connections.ApplicationConnection()) { //get school SqlCommand cmdGetSchool = new SqlCommand("SELECT Id, Name FROM tbl_school", conn); SqlDataReader sqldrGetSchool = cmdGetSchool.ExecuteReader(); try { ddlSchool.Items.Clear(); ddlSchool.Items.Insert(0, new ListItem("-- Select School --", "0")); while (sqldrGetSchool.Read()) { ddlSchool.Items.Add(new ListItem(sqldrGetSchool[1].ToString(), sqldrGetSchool[0].ToString())); } ddlLevel.Items.Clear(); ddlLevel.Items.Insert(0, new ListItem("-- Select Level --", "0")); ddlLevel.Items.Insert(1, new ListItem("Level 1", "1")); ddlLevel.Items.Insert(2, new ListItem("Level 2", "2")); ddlLevel.Items.Insert(3, new ListItem("Level 3", "3")); ddlLevel.Items.Insert(4, new ListItem("Level 4", "4")); } catch (Exception ex) { throw ex; } finally { sqldrGetSchool.Close(); conn.Close(); } //levels } }
//submit private payment protected void btnSubmitPrivatePayment_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { //new value to update db outstand fees if (txtCardNumber.Text.Length == 16 && txtExpiryMonth.Text.Length == 2 && txtExpiryYear.Text.Length == 2 & txtCvCode.Text.Length == 3) { decimal newOutstandingValue = Convert.ToDecimal(lblPrivateFinanceOutstandignFees.Text) - Convert.ToDecimal(txtAmountToPay.Text); lblPaymentError.Text = ""; try { //compare dates for expired cards int currentMonth = DateTime.Now.Month; string year = DateTime.Now.ToString("yy"); int currentyear = Convert.ToInt32(year); if (Convert.ToInt32(txtExpiryMonth.Text) < currentMonth || Convert.ToInt32(txtExpiryYear.Text) < currentyear) { lblProvatePaymentError.Text = "It Apperas The Card Details You Have Entered Are Expired"; } else { if (newOutstandingValue == Convert.ToDecimal(0.00)) { //sets fully paid //update outstanding fees SqlCommand cmdUpdateOutstandigFees = new SqlCommand("UPDATE tbl_outstandingFees SET OutstandingFees = @fees WHERE StudentId = @studentId", conn); cmdUpdateOutstandigFees.Parameters.AddWithValue("@fees", Convert.ToDecimal(0)); cmdUpdateOutstandigFees.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdUpdateOutstandigFees.ExecuteNonQuery(); SqlCommand cmdSubmitPrivateFinancePayment = new SqlCommand("UPDATE tbl_studentUser SET FeesPaid = @feesPaid WHERE StudentId = @studentId", conn); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@feesPaid", Convert.ToInt32(1)); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdSubmitPrivateFinancePayment.ExecuteNonQuery(); } else { //updates outstandg fees to new value SqlCommand cmdUpdateOutstandigFees = new SqlCommand("UPDATE tbl_outstandingFees SET OutstandingFees = @fees WHERE StudentId = @studentId", conn); cmdUpdateOutstandigFees.Parameters.AddWithValue("@fees", Convert.ToDecimal(newOutstandingValue)); cmdUpdateOutstandigFees.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdUpdateOutstandigFees.ExecuteNonQuery(); SqlCommand cmdSubmitPrivateFinancePayment = new SqlCommand("UPDATE tbl_studentUser SET FeesPaid = @feesPaid WHERE StudentId = @studentId", conn); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@feesPaid", Convert.ToInt32(2)); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdSubmitPrivateFinancePayment.ExecuteNonQuery(); } //insert into payment records SqlCommand cmdSubmitPaymentRecord = new SqlCommand("INSERT INTO tbl_paymentRecord (Date,Amount) Values(@date,@amount)"); cmdSubmitPaymentRecord.Parameters.AddWithValue("@date", Convert.ToDateTime(DateTime.Now.ToShortDateString())); cmdSubmitPaymentRecord.Parameters.AddWithValue("@amount", Convert.ToDecimal(newOutstandingValue)); cmdSubmitPaymentRecord.ExecuteNonQuery(); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { conn.Close(); } } else { lblPaymentError.Text = "Please Enter Valid Credit Card Information"; } } progressBar.Attributes.Add("class", "progress-bar progress-bar-success"); ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 100+'%').attr('aria-valuenow', valeur);", true); hideAllPanels(); pnlSuccess.Visible = true; }
private void UploadCSVFile() { SqlConnection conn = Connections.ApplicationConnection(); string filePath = Server.MapPath(Session["s_filePath"].ToString()); StreamReader sr = new StreamReader(filePath); string line = sr.ReadLine(); string[] value = line.Split(','); DataTable dt = new DataTable(); DataRow row; dt.Columns.Add("Forename", typeof(String)); dt.Columns.Add("Surname", typeof(String)); dt.Columns.Add("Role", typeof(Int32)); dt.Columns.Add("Number", typeof(Int32)); dt.Columns.Add("Password", typeof(String)); dt.Columns.Add("EmailAddress", typeof(String)); while (!sr.EndOfStream) { value = sr.ReadLine().Split(','); if (value.Length == dt.Columns.Count) { row = dt.NewRow(); row.ItemArray = value; dt.Rows.Add(row); } } SqlBulkCopy bc = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.KeepIdentity); bc.DestinationTableName = "tbl_user"; bc.BatchSize = dt.Rows.Count; bc.ColumnMappings.Add("Forename", "Forename"); bc.ColumnMappings.Add("Surname", "Surname"); bc.ColumnMappings.Add("Role", "Role"); bc.ColumnMappings.Add("Number", "Number"); bc.ColumnMappings.Add("Password", "Password"); bc.ColumnMappings.Add("EmailAddress", "EmailAddress"); bc.WriteToServer(dt); bc.Close(); conn.Close(); lblSuccess.Text = "Success, Your File Has Been Uploaded "; //-----------------adds last ids to tbl_student-------------------- ArrayList usersArrayList = new ArrayList(); using (SqlConnection connection = Connections.ApplicationConnection()) { SqlCommand cmdGetLastId = new SqlCommand("SELECT TOP (@count) Id FROM tbl_user ORDER BY Id DESC", connection); cmdGetLastId.Parameters.AddWithValue("@count", dt.Rows.Count); SqlDataReader sqldr = cmdGetLastId.ExecuteReader(); try { while (sqldr.Read()) { usersArrayList.Add(sqldr["Id"]); } } catch (Exception ex) { throw ex; } finally { connection.Close(); } } usersArrayList.Sort(); foreach (var val in usersArrayList) { Debug.WriteLine("VAL : " + val); } using (SqlConnection connection = Connections.ApplicationConnection()) { SqlCommand cmdInsertId = new SqlCommand("INSERT INTO tbl_studentUser (StudentId, Level) VALUES (@id, @level)", connection); try { foreach (var student in usersArrayList) { cmdInsertId.Parameters.Clear(); cmdInsertId.Parameters.AddWithValue("@id", student); cmdInsertId.Parameters.AddWithValue("@level", 0); cmdInsertId.ExecuteNonQuery(); } } catch (Exception ex) { throw ex; } finally { connection.Close(); } } }
private void PopulateDropDowns() { using (SqlConnection conn = Connections.ApplicationConnection()) { //----------------load ddl values--------------- //get courses SqlCommand cmdGetCourse = new SqlCommand("SELECT Id, Name FROM tbl_course", conn); SqlDataReader sqldrGetCourse = cmdGetCourse.ExecuteReader(); try { ddlCourse.Items.Clear(); while (sqldrGetCourse.Read()) { ddlCourse.Items.Add(new ListItem(sqldrGetCourse[1].ToString(), sqldrGetCourse[0].ToString())); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { sqldrGetCourse.Close(); } //levels ddlLevel.Items.Add(new ListItem("Level 1", "1")); ddlLevel.Items.Add(new ListItem("Level 2", "2")); ddlLevel.Items.Add(new ListItem("Level 3", "3")); ddlLevel.Items.Add(new ListItem("Level 4", "4")); //get semesters SqlCommand cmdGetSemester = new SqlCommand("SELECT Id, Semester FROM tbl_semester", conn); SqlDataReader sqldrGetSemester = cmdGetSemester.ExecuteReader(); try { ddlSemester.Items.Clear(); while (sqldrGetSemester.Read()) { ddlSemester.Items.Add(new ListItem(sqldrGetSemester[1].ToString(), sqldrGetSemester[0].ToString())); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { sqldrGetSemester.Close(); } //get days SqlCommand cmdGetDays = new SqlCommand("SELECT Id, Day FROM tbl_days", conn); SqlDataReader sqldrGetDays = cmdGetDays.ExecuteReader(); try { ddlDay.Items.Clear(); while (sqldrGetDays.Read()) { ddlDay.Items.Add(new ListItem(sqldrGetDays[1].ToString(), sqldrGetDays[0].ToString())); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { sqldrGetDays.Close(); } //class type ddl SqlCommand cmdGetClassType = new SqlCommand("SELECT Id, Type FROM tbl_classType", conn); SqlDataReader sqldrGetClassType = cmdGetClassType.ExecuteReader(); try { while (sqldrGetClassType.Read()) { ddlClassType.Items.Add(new ListItem(sqldrGetClassType[1].ToString(), sqldrGetClassType[0].ToString())); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { sqldrGetClassType.Close(); conn.Close(); } } }
//submit payment protected void btnSubmitPrivatePayment_Click(object sender, EventArgs e) { decimal outstandingFees = Convert.ToDecimal(lblOutstandingFees.Text); decimal payingFees = Convert.ToInt32(txtAmountToPay.Text); using (SqlConnection conn = Connections.ApplicationConnection()) { //new value to update db outstand fees if (txtCardNumber.Text.Length == 16 && txtExpiryMonth.Text.Length == 2 && txtExpiryYear.Text.Length == 2 & txtCvCode.Text.Length == 3) { decimal newOutstandingValue = Convert.ToDecimal(lblOutstandingFees.Text) - Convert.ToDecimal(txtAmountToPay.Text); lblPaymentError.Text = ""; if (newOutstandingValue < 0) { lblPaymentError.Text = "Please Enter A Payment Amout Greater Than The Students Outstanding Fees"; } try { //compare dates for expired cards int currentMonth = DateTime.Now.Month; string year = DateTime.Now.ToString("yy"); int currentyear = Convert.ToInt32(year); if (Convert.ToInt32(txtExpiryMonth.Text) < currentMonth || Convert.ToInt32(txtExpiryYear.Text) < currentyear) { lblPaymentError.Text = "It Apperas The Card Details You Have Entered Are Expired"; } else { if (newOutstandingValue == Convert.ToDecimal(0.00)) { //sets fully paid //update outstanding fees SqlCommand cmdUpdateOutstandigFees = new SqlCommand("UPDATE tbl_outstandingFees SET OutstandingFees = @fees WHERE StudentId = @studentId", conn); cmdUpdateOutstandigFees.Parameters.AddWithValue("@fees", Convert.ToDecimal(0)); cmdUpdateOutstandigFees.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_studentId"])); cmdUpdateOutstandigFees.ExecuteNonQuery(); SqlCommand cmdSubmitPrivateFinancePayment = new SqlCommand("UPDATE tbl_studentUser SET FeesPaid = @feesPaid WHERE StudentId = @studentId", conn); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@feesPaid", Convert.ToInt32(1)); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_studentId"])); cmdSubmitPrivateFinancePayment.ExecuteNonQuery(); } else { //updates outstandg fees to new value SqlCommand cmdUpdateOutstandigFees = new SqlCommand("UPDATE tbl_outstandingFees SET OutstandingFees = @fees WHERE StudentId = @studentId", conn); cmdUpdateOutstandigFees.Parameters.AddWithValue("@fees", Convert.ToDecimal(newOutstandingValue)); cmdUpdateOutstandigFees.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_studentId"])); cmdUpdateOutstandigFees.ExecuteNonQuery(); SqlCommand cmdSubmitPrivateFinancePayment = new SqlCommand("UPDATE tbl_studentUser SET FeesPaid = @feesPaid WHERE StudentId = @studentId", conn); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@feesPaid", Convert.ToInt32(2)); cmdSubmitPrivateFinancePayment.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_studentId"])); cmdSubmitPrivateFinancePayment.ExecuteNonQuery(); } //insert into payment records SqlCommand cmdSubmitPaymentRecord = new SqlCommand("INSERT INTO tbl_paymentRecord (Date,Amount) Values(@date,@amount)"); cmdSubmitPaymentRecord.Parameters.AddWithValue("@date", Convert.ToDateTime(DateTime.Now.ToShortDateString())); cmdSubmitPaymentRecord.Parameters.AddWithValue("@amount", Convert.ToDecimal(newOutstandingValue)); cmdSubmitPaymentRecord.ExecuteNonQuery(); lblSuccess.Text = "Success, Student Fee Details Have Been Updated"; HideAllPanels(); pnlStudentList.Visible = true; } } catch (Exception ex) { Debug.WriteLine(ex); } finally { conn.Close(); } } else { lblPaymentError.Text = "Please Enter Valid Credit Card Information"; } } }
//personal details step 1 submit protected void btnSubmitStep1_Click(object sender, EventArgs e) { using (SqlConnection conn = Connections.ApplicationConnection()) { //update personal details tbl_user SqlCommand cmdUpdatePersonalDetails = new SqlCommand("UPDATE tbl_user SET Forename = @forename, Surname = @surname WHERE Id = @id ", conn); cmdUpdatePersonalDetails.Parameters.AddWithValue("@id", Convert.ToInt32(Session["s_loggedUserId"])); cmdUpdatePersonalDetails.Parameters.AddWithValue("@forename", txtPersonalForename.Text.ToString()); cmdUpdatePersonalDetails.Parameters.AddWithValue("@surname", txtPersonalSurname.Text.ToString()); //update address details tbl_studentUser SqlCommand cmdUpdateAddressDetails = new SqlCommand("UPDATE tbl_studentUser SET HomeHouseNo = @homeHouseNo, HomeStreet = @homeStreet, HomeTown_City = @homeTown_city, HomeCountry = @homeCountry, TermHouseNo = @termHouseNo, TermStreet = @termStreet, TermTown_City = @termTown_City, TermCountry = @termCountry, PhoneNumber = @phoneNumber, NextOfKinForename = @nextOfKinForename, NextOfKinSurname = @nextOfKinSurname, NextOfKinHouseNo = @nextOfKinHouseNo, NextOfKinStreet = @nextOfKinStreet, NextOfKinTown_City = @nextOfKinTown_City , NextOfKinCountry = @nextOfKinCountry, NextOfKinEmail = @nextOfKinEmail, NextOfKinPhone = @nextOfKinPhone WHERE StudentId = @studentId", conn); cmdUpdateAddressDetails.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdUpdateAddressDetails.Parameters.AddWithValue("@homeHouseNo", txtHomeAddressNo.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@homeStreet", txtHomeAddressStreet.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@homeTown_city", txtHomeAddressTown_City.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@homeCountry", txtHomeAddressCountry.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@termHouseNo", txtTermAddressHouseNo.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@termStreet", txtTermAddressStreet.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@termTown_City", txtTermAddressTown.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@termCountry", txtTermAddressCountry.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@phoneNumber", txtPersonalPhoneNumber.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinForename", txtNextOfKinForename.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinSurname", txtNextOfKinSurname.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinHouseNo", txtNextOfKinAddressHouseNo.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinStreet", txtNextOfKinAddressStreet.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinTown_City", txtNextOfKinAddressTown.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinCountry", txtNextOfKinAddressCountry.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinEmail", txtNextOfKinEmail.Text.ToString()); cmdUpdateAddressDetails.Parameters.AddWithValue("@nextOfKinPhone", txtNextOfKinPhoneNumber.Text.ToString()); //update level + 1 int level; SqlCommand cmdGetLevel = new SqlCommand("SELECT StudentLevel FROM tbl_studentUser WHERE StudentId = @studentId", conn); cmdGetLevel.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); level = Convert.ToInt32(cmdGetLevel.ExecuteScalar()); //TODO uncomment after testing if (level != 1 && level < 5) { //level = level + 1; } Session["s_studentLevel"] = level.ToString(); Debug.WriteLine("STUDENT LEVEL: " + Session["s_studentLevel"]); SqlCommand cmdUpdateLevel = new SqlCommand("UPDATE tbl_studentUser SET StudentLevel = @studentLevel WHERE StudentId = @studentId", conn); cmdUpdateLevel.Parameters.AddWithValue("@studentLevel", Convert.ToInt32(level)); cmdUpdateLevel.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); try { cmdUpdatePersonalDetails.ExecuteNonQuery(); cmdUpdateAddressDetails.ExecuteNonQuery(); //cmdUpdateLevel.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine("EXCEPTION : " + ex); } finally { conn.Close(); } ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 33+'%').attr('aria-valuenow', valeur);", true); hideAllPanels(); pnlStep2.Visible = true; } }
protected void Page_Load(object sender, EventArgs e) { if (Session["s_loggedUserId"] == null || Session["s_loggedUserRole"].ToString() != "10") { Response.Redirect("~/Default.aspx"); } Debug.WriteLine("LOGGED USER ID : " + Session["s_loggedUserId"]); //CheckDate(); //sets enter button for each panel if (pnlStep1.Visible == true) { this.Page.Form.DefaultButton = btnSubmitStep1.UniqueID; } if (!IsPostBack) { //loads data from table into form using (SqlConnection conn = Connections.ApplicationConnection()) { //personal details SqlCommand cmdPersonalDetails = new SqlCommand("SELECT Number,Forename,Surname,EmailAddress FROM tbl_user WHERE Id = @id", conn); cmdPersonalDetails.Parameters.AddWithValue("@id", Convert.ToInt32(Session["s_loggedUserId"])); using (SqlDataReader rdrPersonalDetails = cmdPersonalDetails.ExecuteReader()) { while (rdrPersonalDetails.Read()) { txtPersonalStudentNumber.Text = rdrPersonalDetails["Number"].ToString(); txtPersonalForename.Text = rdrPersonalDetails["Forename"].ToString(); txtPersonalSurname.Text = rdrPersonalDetails["Surname"].ToString(); txtPersonalEmail.Text = rdrPersonalDetails["EmailAddress"].ToString(); } } //home address, term address & next of kin details SqlCommand cmdAddressInfo = new SqlCommand("SELECT HomeHouseNo,HomeStreet,HomeTown_City, HomeCountry, TermHouseNo, TermStreet, TermTown_City, TermCountry, PhoneNumber, NextOfKinForename, NextOfKinSurname, NextOfKinHouseNo, NextOfKinStreet, NextOfKinTown_City, NextOfKinCountry, NextOfKinEmail, NextOfKinPhone FROM tbl_studentUser WHERE StudentId = @studentId", conn); cmdAddressInfo.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); using (SqlDataReader rdrAddressDetais = cmdAddressInfo.ExecuteReader()) { while (rdrAddressDetais.Read()) { //home address values txtHomeAddressNo.Text = rdrAddressDetais["HomeHouseNo"].ToString(); txtHomeAddressStreet.Text = rdrAddressDetais["HomeStreet"].ToString(); txtHomeAddressTown_City.Text = rdrAddressDetais["HomeTown_City"].ToString(); txtHomeAddressCountry.Text = rdrAddressDetais["HomeCountry"].ToString(); //term address values txtTermAddressHouseNo.Text = rdrAddressDetais["TermHouseNo"].ToString(); txtTermAddressStreet.Text = rdrAddressDetais["TermStreet"].ToString(); txtTermAddressTown.Text = rdrAddressDetais["TermTown_City"].ToString(); txtTermAddressCountry.Text = rdrAddressDetais["TermCountry"].ToString(); //phone number txtPersonalPhoneNumber.Text = rdrAddressDetais["PhoneNumber"].ToString(); //next of kin values txtNextOfKinForename.Text = rdrAddressDetais["NextOfKinForename"].ToString(); txtNextOfKinSurname.Text = rdrAddressDetais["NextOfKinSurname"].ToString(); txtNextOfKinAddressHouseNo.Text = rdrAddressDetais["NextOfKinHouseNo"].ToString(); txtNextOfKinAddressStreet.Text = rdrAddressDetais["NextOfKinStreet"].ToString(); txtNextOfKinAddressTown.Text = rdrAddressDetais["NextOfKinTown_City"].ToString(); txtNextOfKinAddressCountry.Text = rdrAddressDetais["NextOfKinCountry"].ToString(); txtNextOfKinEmail.Text = rdrAddressDetais["NextOfKinEmail"].ToString(); txtNextOfKinPhoneNumber.Text = rdrAddressDetais["NextOfKinPhone"].ToString(); } } //close connection conn.Close(); } } }
//--------------------Create a team------------------------------------------ //create team row command protected void gvModules_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "cmdCreateTeam") { var clickedButton = e.CommandSource as Button; var clickedRow = clickedButton.NamingContainer as GridViewRow; string moduleTitle = clickedRow.Cells[1].Text; TextBox txtTeamName = (TextBox)clickedRow.FindControl("txtTeamName"); string teamName = moduleTitle + " - " + txtTeamName.Text; //insert into tbl_studentTeams using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdInsertTeam = new SqlCommand("INSERT INTO tbl_studentTeams (ModuleId, TeamName) VALUES (@moduleId, @teamName)", conn); cmdInsertTeam.Parameters.AddWithValue("@moduleId", Convert.ToInt32(clickedRow.Cells[0].Text)); cmdInsertTeam.Parameters.AddWithValue("@teamName", Convert.ToString(teamName)); try { cmdInsertTeam.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } //get the team id of the last created using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetIdOfCreatedTeam = new SqlCommand("SELECT Id FROM tbl_studentTeams WHERE ModuleId = @moduleId AND TeamName = @teamName", conn); cmdGetIdOfCreatedTeam.Parameters.AddWithValue("@moduleId", Convert.ToInt32(clickedRow.Cells[0].Text)); cmdGetIdOfCreatedTeam.Parameters.AddWithValue("@teamName", Convert.ToString(teamName)); try { Session["s_createdTeamId"] = Convert.ToString(cmdGetIdOfCreatedTeam.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } //insert logged user as first team members using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdInsertTeamMember = new SqlCommand("INSERT INTO tbl_studentTeamMembers (TeamId, MemberId) VALUES (@teamId, @memberId)", conn); cmdInsertTeamMember.Parameters.AddWithValue("@teamId", Convert.ToInt32(Session["s_createdTeamId"])); cmdInsertTeamMember.Parameters.AddWithValue("@memberId", Convert.ToInt32(Session["s_loggedUserId"])); try { cmdInsertTeamMember.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); txtTeamName.Text = ""; lblCreateTeamSuccess.Text = "Your Team Has Been Successfully Created !"; } } } }
private void PopulateDropDowns() { using (SqlConnection conn = Connections.ApplicationConnection()) { //get school SqlCommand cmdGetSchol = new SqlCommand("SELECT Id, Name FROM tbl_school", conn); SqlDataReader sqldrGetSchool = cmdGetSchol.ExecuteReader(); try { ddlSchool.Items.Clear(); ddlSchool.Items.Insert(0, new ListItem("-- Select School --", "0")); while (sqldrGetSchool.Read()) { ddlSchool.Items.Add(new ListItem(sqldrGetSchool[1].ToString(), sqldrGetSchool[0].ToString())); } ddlCourse.Items.Insert(0, new ListItem("-- Please Select A School --", "0")); } catch (Exception ex) { throw ex; } finally { sqldrGetSchool.Close(); } //get days SqlCommand cmdGetDays = new SqlCommand("SELECT Id, Day FROM tbl_days", conn); SqlDataReader sqldrGetDays = cmdGetDays.ExecuteReader(); try { ddlDay.Items.Clear(); while (sqldrGetDays.Read()) { ddlDay.Items.Add(new ListItem(sqldrGetDays[1].ToString(), sqldrGetDays[0].ToString())); } } catch (Exception ex) { throw ex; } finally { sqldrGetDays.Close(); } //class type ddl SqlCommand cmdGetClassType = new SqlCommand("SELECT Id, Type FROM tbl_classType", conn); SqlDataReader sqldrGetClassType = cmdGetClassType.ExecuteReader(); try { ddlClassType.Items.Clear(); ddlClassType.Items.Insert(0, new ListItem("-- Select An Event Type --", "0")); while (sqldrGetClassType.Read()) { ddlClassType.Items.Add(new ListItem(sqldrGetClassType[1].ToString(), sqldrGetClassType[0].ToString())); } } catch (Exception ex) { throw ex; } finally { sqldrGetClassType.Close(); conn.Close(); } } }
protected void gvSpecChanges_RowCommand(object sender, GridViewCommandEventArgs e) { var clickedButton = e.CommandSource as Button; var clickedRow = clickedButton.NamingContainer as GridViewRow; int moduleId = Convert.ToInt32(clickedRow.Cells[0].Text); int changerId = Convert.ToInt32(clickedRow.Cells[3].Text); int changeId = Convert.ToInt32(clickedRow.Cells[5].Text); string changes = clickedRow.Cells[6].Text; string moduleName = clickedRow.Cells[1].Text; //approve changes if (e.CommandName == "cmdApproveChanges") { using (SqlConnection conn = Connections.ApplicationConnection()) { //update module description SqlCommand cmdUpdateDescription = new SqlCommand("UPDATE tbl_modules SET ModuleDescription = @description WHERE Id = @id", conn); cmdUpdateDescription.Parameters.AddWithValue("@id", moduleId); cmdUpdateDescription.Parameters.AddWithValue("@description", changes); //delete from pendig changes table SqlCommand cmdDeleteChanges = new SqlCommand("DELETE FROM tbl_pendingModuleDescriptionChanges WHERE Id = @id ", conn); cmdDeleteChanges.Parameters.AddWithValue("@id", changeId); try { cmdUpdateDescription.ExecuteNonQuery(); cmdDeleteChanges.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); gvSpecChanges.DataBind(); lblError_Success.CssClass = "text-success"; lblError_Success.Text = "Description CHanges Have Been Successfully Approved And Updated"; } } } //deny changes if (e.CommandName == "cmdDenyChanges") { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdDeleteChanges = new SqlCommand("DELETE FROM tbl_pendingModuleDescriptionChanges WHERE Id = @id ", conn); cmdDeleteChanges.Parameters.AddWithValue("@id", changeId); string address = ""; SqlCommand cmdGetEmailAddress = new SqlCommand("SELECT EmailAddress FROM tbl_user WHERE Id = @id", conn); cmdGetEmailAddress.Parameters.AddWithValue("@id", changerId); address = Convert.ToString(cmdGetEmailAddress.ExecuteScalar()); try { cmdDeleteChanges.ExecuteNonQuery(); //send email to requester with denial string emailHeader = "Description Changes Denied"; string emailSubject = "Description Changes Denied For Module: " + moduleName; string emailBody = "Your module description change of : " + changes + " has been denied by the module academic program manager."; MailAddress mailAddress = new MailAddress(address.ToString()); string emailFileName = ""; string emailFile = ""; //send email Email.SendEmail(mailAddress, emailHeader, emailSubject, emailBody, emailFileName, emailFile); } catch (Exception ex) { throw ex; } finally { conn.Close(); gvSpecChanges.DataBind(); lblError_Success.CssClass = "text-danger"; lblError_Success.Text = "Description Changes Have Been Successfully Denied And Have Not Been Updated"; } } } }
protected void Page_Load(object sender, EventArgs e) { if (Session["s_loggedUserId"] == null || Session["s_loggedUserRole"].ToString() != "20") { if (Session["s_loggedUserRole"].ToString() == "40") { } else if (Session["s_loggedUserRole"].ToString() == "30") { } else if (Session["s_loggedUserRole"].ToString() == "60") { } else { Response.Redirect("~/Default.aspx"); } } this.Page.Form.DefaultButton = btnFilterStudents.UniqueID; this.Page.Form.DefaultFocus = txtStudentNumber.ClientID; //populat date drop down if (!IsPostBack) { using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetYears = new SqlCommand("SELECT Id, Year FROM tbl_years ORDER BY Year DESC", conn); SqlDataReader sqldrGetYears = cmdGetYears.ExecuteReader(); try { ddlYear.Items.Clear(); while (sqldrGetYears.Read()) { ddlYear.Items.Add(new ListItem(sqldrGetYears[1].ToString(), sqldrGetYears[0].ToString())); } } catch (Exception ex) { Debug.WriteLine(ex); } finally { sqldrGetYears.Close(); conn.Close(); } } using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetLastYear = new SqlCommand("SELECT TOP 1 Id FROM tbl_years ORDER BY Id DESC", conn); try { Session["s_classYearId"] = Convert.ToInt32(cmdGetLastYear.ExecuteScalar()); } catch (Exception ex) { Debug.WriteLine("EXCEPTION " + ex); } finally { conn.Close(); } } } }
protected void btnSubmitManualEnrolement_Click(object sender, EventArgs e) { string studentId = ""; string studentName = ""; string studentNumber = ""; string studentEmail = ""; string advisorId = ""; string advisorEmail = ""; using (SqlConnection conn = Connections.ApplicationConnection()) { //--------------gets student information for logegd in user -------------------------- SqlCommand cmdGetStudentInfo = new SqlCommand("SELECT Id, CONCAT(Forename, '' ,Surname) AS FullName, Number, EmailAddress FROM tbl_user WHERE Id = @id ", conn); cmdGetStudentInfo.Parameters.AddWithValue("@Id", Convert.ToInt32(Session["s_loggedUserId"])); SqlDataReader sqldrGetStudentInfo = cmdGetStudentInfo.ExecuteReader(); try { while (sqldrGetStudentInfo.Read()) { studentId = sqldrGetStudentInfo["Id"].ToString(); studentName = sqldrGetStudentInfo["FullName"].ToString(); studentNumber = sqldrGetStudentInfo["Number"].ToString(); studentEmail = sqldrGetStudentInfo["EmailAddress"].ToString(); } } catch (Exception ex) { throw ex; } finally { sqldrGetStudentInfo.Close(); } //------------------gets email for students advisor of studies------------------------- SqlCommand cmdgetAdvisor = new SqlCommand("SELECT tbl_user.Id, tbl_user.EmailAddress FROM tbl_user INNER JOIN tbl_advisorOfStudies ON tbl_user.Id = tbl_advisorOfStudies.AcademicId WHERE (tbl_advisorOfStudies.StudentId = @studentId)", conn); cmdgetAdvisor.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); SqlDataReader sqldrGetAdvisorInfo = cmdgetAdvisor.ExecuteReader(); try { while (sqldrGetAdvisorInfo.Read()) { advisorId = sqldrGetAdvisorInfo["Id"].ToString(); advisorEmail = sqldrGetAdvisorInfo["EmailAddress"].ToString(); } } catch (Exception ex) { throw ex; } finally { sqldrGetAdvisorInfo.Close(); } ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 100+'%').attr('aria-valuenow', valeur);", true); progressBar.Attributes.Add("class", "progress-bar progress-bar-success"); //------------------inset into help table for response ------------------------- SqlCommand cmdInsertAdvisorHelpTable = new SqlCommand("INSERT INTO tbl_advisorEnrolementHelp (StudentId, AdvisorId, RequestDate) VALUES (@studentId, @advisorId, @requestDate)", conn); cmdInsertAdvisorHelpTable.Parameters.AddWithValue("@studentId", Convert.ToInt32(studentId)); cmdInsertAdvisorHelpTable.Parameters.AddWithValue("@advisorId", Convert.ToInt32(advisorId)); cmdInsertAdvisorHelpTable.Parameters.AddWithValue("@requestDate", Convert.ToDateTime(DateTime.Now.ToShortDateString())); try { cmdInsertAdvisorHelpTable.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } //---------sends email to advisor of studies------------- string emailHeader = "Manual Enrolement Request - " + studentName; string emailSubject = studentNumber + " : " + studentName + " " + "Has Requested Manual Enrolement"; string emailBody = studentNumber + " : " + studentName + " " + "Has Requested Manual Enrolement They Can Be Contacted Here To Arrange A Meeting - " + studentEmail; MailAddress mailAddress = new MailAddress(advisorEmail.ToString()); string emailFileName = ""; string emailFile = ""; //send email Email.SendEmail(mailAddress, emailHeader, emailSubject, emailBody, emailFileName, emailFile); lblManualEnrolementSuccess.Text = "Your Request Has Been Succesfull, Your Advisor Of Studies Will Be In Touch. Please Keep A Lookout In Your Inbox " + studentEmail; btnSubmitManualEnrolement.Visible = false; btnReturnHome.Visible = true; }
//sort array of time + day //----------------module submit click -------------------------- protected void btnSubmitModules_Click(object sender, EventArgs e) { //adds CAT POINTS Int32 overallCatPoints = 0; //class dayId + time array List <Classes> classTimesList = new List <Classes>(); //modules to enroll array ArrayList modulesToEnrollArray = new ArrayList(); //time clashes flag bool clashesFlag = false; //day + time variables string dayId; string time; //already enrolled array list ArrayList alreadyEnrolledIdArrayList = new ArrayList(); //----------loops through list view---------- foreach (ListViewItem item in lvModules.Items) { //gets checked modules CheckBox checkedModule = (CheckBox)item.FindControl("cbPickModule"); if (checkedModule.Checked == true) { //adds cat points Label catPoint = (Label)item.FindControl("lblCatPoints"); overallCatPoints = overallCatPoints + Convert.ToInt32(catPoint.Text); //----------GETS TIMETABLE DATA----------------------- Label moduleId = (Label)item.FindControl("lblModuleId"); //adds to moudles to enroll in modulesToEnrollArray.Add(moduleId.Text); //gets classtimes + adds time & day to array of objects using (SqlConnection conn = Connections.ApplicationConnection()) { SqlCommand cmdGetClassTimes = new SqlCommand("SELECT tbl_timeTable.DayId, tbl_timeTable.Time FROM tbl_timeTable WHERE (tbl_timeTable.ModuleId = @moduleId)", conn); cmdGetClassTimes.Parameters.AddWithValue("@moduleId", moduleId.Text); using (SqlDataReader sqldr = cmdGetClassTimes.ExecuteReader()) { while (sqldr.Read()) { dayId = sqldr["DayId"].ToString(); time = sqldr["Time"].ToString(); classTimesList.Add(new Classes() { DayId = dayId, Time = time }); } sqldr.Close(); } conn.Close(); } } } //sorts array List <Classes> ClassesSortedList = classTimesList.OrderBy(o => o.DayId).ToList(); //checks for time table clashes string classAtI = ""; string classAtJ = ""; for (int i = 1; i < ClassesSortedList.Count; i++) { classAtI = ClassesSortedList[i].DayId + " " + ClassesSortedList[i].Time; classAtJ = ClassesSortedList[i - 1].DayId + " " + ClassesSortedList[i - 1].Time; if (classAtI.Equals(classAtJ)) { clashesFlag = true; break; } } //enrolled module ID session for enrolement timetable output //Session["s_moudleEnrolledArrrayList"] = modulesToEnrollArray; //------------------checks if selected cat points = 120 OR clashesFlag = true - else go to step 3--------------------- if (overallCatPoints != 120 || clashesFlag == true) { if (overallCatPoints != 120) { lblStep2Error.Text = "The Modules You Have Selected Do Not Add Up To 120 CAT Points"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 33+'%').attr('aria-valuenow', valeur);", true); } if (clashesFlag == true) { lblStep2Error.Text = "The Modules You Have Selected Clash With Eachother Please Review Your Selections"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 33+'%').attr('aria-valuenow', valeur);", true); } } else //enroll and get + set outstanfing fees { ScriptManager.RegisterStartupScript(Page, Page.GetType(), "progressBar", "$('.progress-bar').css('width', 66+'%').attr('aria-valuenow', valeur);", true); hideAllPanels(); pnlStep3.Visible = true; //enroll in selected modules using (SqlConnection conn = Connections.ApplicationConnection()) { try { //gets fee price for user decimal feeCost; SqlCommand cmdGetFees = new SqlCommand("SELECT tbl_feesType.Price FROM tbl_feesType INNER JOIN tbl_studentUser ON tbl_feesType.Id = tbl_studentUser.FeesType WHERE (tbl_studentUser.StudentId = @loggedUserId)", conn); cmdGetFees.Parameters.AddWithValue("@loggedUserId", Convert.ToInt32(Session["s_loggedUserId"])); feeCost = (decimal)cmdGetFees.ExecuteScalar(); Debug.WriteLine("FEE COST : " + feeCost); lblStudentFinanceCost.Text = Convert.ToString(feeCost); //delete current outstanding fee record //SqlCommand cmdDeleteOutstandingFees = new SqlCommand("DELETE FROM tbl_outstandingFees WHERE StudentId = @studentId", conn); //cmdDeleteOutstandingFees.Parameters.AddWithValue("@studentFees", Convert.ToInt32(Session["s_loggedUserId"])); //cmdDeleteOutstandingFees.ExecuteNonQuery(); Debug.WriteLine("FEE COST BEFORE INSERT : " + feeCost); //inserts into outstanding fees SqlCommand cmdInsertOutstandingFees = new SqlCommand("INSERT INTO tbl_outstandingFees (StudentId, OutstandingFees) VALUES (@studentId, @outstandingFees)", conn); cmdInsertOutstandingFees.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdInsertOutstandingFees.Parameters.AddWithValue("@outstandingFees", Convert.ToDecimal(feeCost)); cmdInsertOutstandingFees.ExecuteNonQuery(); //get module ids from tbl_studentModule SqlCommand cmdGetAlreadyEnrolledModuleId = new SqlCommand("SELECT ModuleId FROM tbl_studentModule WHERE StudentId = @studentId", conn); cmdGetAlreadyEnrolledModuleId.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); using (SqlDataReader sqldr = cmdGetAlreadyEnrolledModuleId.ExecuteReader()) { while (sqldr.Read()) { alreadyEnrolledIdArrayList.Add(sqldr["ModuleId"]); } } } catch (Exception ex) { Debug.WriteLine(ex); } //insert enrolled modules foreach (var m in modulesToEnrollArray) { SqlCommand cmdInsertModulesToEnroll = new SqlCommand("INSERT INTO tbl_studentModule (StudentId, ModuleId, YearId, Complete) VALUES (@studentId, @moduleId, @yearId, @complete)", conn); cmdInsertModulesToEnroll.Parameters.AddWithValue("@studentId", Convert.ToInt32(Session["s_loggedUserId"])); cmdInsertModulesToEnroll.Parameters.AddWithValue("@moduleId", Convert.ToInt32(m)); cmdInsertModulesToEnroll.Parameters.AddWithValue("@yearId", Convert.ToInt32(11)); cmdInsertModulesToEnroll.Parameters.AddWithValue("@complete", Convert.ToInt32(0)); cmdInsertModulesToEnroll.ExecuteNonQuery(); } conn.Close(); } } }