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