// delete from student major
public Boolean DeleteStudentProgramme(StudentMajorVO inStudentMajor)
{
bool status = false;
try
{
DBConnection.conn.Open();
String query = "DELETE FROM dbo.StudentMajor WHERE StudentId=@StudentId";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@StudentId", inStudentMajor.studentId);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(status);
}
public Boolean AddStudent(StudentMajorVO inStudentMajor)
{
Boolean studentId = false;
try{
studentId = studentMajorDao.AddStudentProgramme(inStudentMajor);
}
catch (CustomException e)
{
throw e;
}
return(true);
}
public StudentMajorVO GetStudentMajor(int studentId)
{
StudentMajorVO studentMajorVO = new StudentMajorVO();
try
{
studentMajorVO = studentMajorDao.GetStudentMajor(studentId);
}
catch (SqlException e)
{
throw e;
}
return(studentMajorVO);
}
//Get list of programme and major for the student
public List <StudentMajorVO> GetStudentMajorList(int studentId)
{
List <StudentMajorVO> studentMajorList = new List <StudentMajorVO>();
try
{
DBConnection.conn.Open();
string query = "SELECT ProgrammeId,MajorId from StudentMajor WHERE StudentId=@StudentId AND Active='Yes'";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@StudentId", studentId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
StudentMajorVO studentMajorVO;
while (reader.Read())
{
studentMajorVO = new StudentMajorVO();
studentMajorVO.ProgrammeID = reader["ProgrammeId"].ToString();
studentMajorVO.MajorID = reader["MajorId"].ToString();
studentMajorList.Add(studentMajorVO);
}
reader.Close();
}
}
catch (SqlException ex)
{
ExceptionUtility.LogException(ex, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + ex.Message);
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + ex.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(studentMajorList);
}
// Add the programme and major for student in StudentMajor table
public Boolean AddStudentProgramme(StudentMajorVO inStudentMajor)
{
bool status = false;
try
{
DBConnection.conn.Open();
String query = "INSERT INTO dbo.StudentMajor (StudentId, ProgrammeId, MajorId, Active) VALUES (@StudentId, @ProgrammeId, @MajorId, @Active)";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@StudentId", inStudentMajor.studentId);
cmd.Parameters.AddWithValue("@ProgrammeId", inStudentMajor.programmeId);
cmd.Parameters.AddWithValue("@MajorId", inStudentMajor.majorId);
cmd.Parameters.AddWithValue("@Active", inStudentMajor.active);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(status);
}
protected void btnAddStudent_Click(object sender, EventArgs e)
{
try
{
DBConnection.conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.Student WHERE StudentID =" + Convert.ToInt32(txtStudentID.Text.Trim()), DBConnection.conn);
Int32 count = (Int32)cmd.ExecuteScalar();
DBConnection.conn.Close();
if (count > 0)
{
Response.Write("<script>alert('StudentID already exists.');</script>");
}
else if (!txtEmail.Text.Trim().EndsWith("@ess.ais.ac.nz"))
{
Response.Write("<script>alert('Please enter valid email (e.g. [email protected])');</script>");
}
DBConnection.conn.Open();
SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM dbo.IlmpUser WHERE EmailId ='" + txtEmail.Text.Trim() + "'", DBConnection.conn);
Int32 count1 = (Int32)cmd1.ExecuteScalar();
DBConnection.conn.Close();
if (count1 > 0)
{
Response.Write("<script>alert('Email already exists, please check email Student');</script>");
}
else
{
bool valid = ValidateControls();
if (valid)
{
int studentID = 0;
try
{
studentID = Int32.Parse(txtStudentID.Text);
}
catch (ParseException)
{
Response.Write("<script>alert('Please enter valid studentid');</script>");
}
string firstName = txtFirstName.Text;
string lastName = txtLastName.Text;
string status = "Studying";
string emailID = txtEmail.Text;
DateTime createdDTM = DateTime.Now;
DateTime updatedDTM = DateTime.Now;
StudentVO studentVO = new StudentVO(studentID, firstName, lastName, status, createdDTM, updatedDTM);
UserVO userVO = new UserVO();
userVO.StudentID = studentID;
userVO.StaffID = 0;
userVO.EmailID = emailID;
userVO.Role = ApplicationConstants.StudentRole;
if (lbMajor.Items.Count == 0)
{
Response.Write("<script>alert('Please choose major');</script>");
}
else if (studentBO.AddStudent(studentVO))
{
for (int i = 0; i < lbMajor.Items.Count; i++)
{
ListItem lmajor = lbMajor.Items[i];
StudentMajorVO studentMajorVO = new StudentMajorVO(studentID, ddlProgramme.SelectedItem.Value, lmajor.Value, "Yes");
StudentMajorBO studentMajorBO = new StudentMajorBO();
if (studentMajorBO.AddStudent(studentMajorVO))
{
Response.Write("<script>alert('Student added successfully');</script>");
}
else
{
Response.Write("<script>alert('Error in adding student major');</script>");
}
}
UserBO userBO = new UserBO();
userBO.AddUser(userVO);
}
else
{
Response.Write("<script>alert('Error in adding student');</script>");
}
}
else
{
Response.Write("<script>alert('Please enter all student details');</script>");
}
}
}
catch (CustomException ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('Error in adding student');</script>");
ExceptionUtility.LogException(ex, "Error Page");
}
Clear();
}
private void SetInitialRowInGrids()
{
ILMPVO ilmpVO = new ILMPVO();
ilmpVO = ilmpBO.GetILMPCoursesWorkshopForId(ilmpId);
populateILMPDetails(ilmpVO);
StudentMajorBO studentMajorBO = new StudentMajorBO();
studentMajorVO = studentMajorBO.GetStudentMajor(ilmpVO.StudentId);
// ILMPTemplateVO ilmpTemplateVO = ilmpTemplateBO.GetTemplateForId(ilmpVO.TemplateId);
if (ilmpVO.IlmpCourses.Count > 0)
{
DataTable dt = new DataTable();
DataRow dr = null;
//Define the Columns
dt.Columns.Add(new DataColumn("Column1", typeof(string)));
dt.Columns.Add(new DataColumn("Column2", typeof(string)));
dt.Columns.Add(new DataColumn("Column3", typeof(string)));
dt.Columns.Add(new DataColumn("Column4", typeof(string)));
dt.Columns.Add(new DataColumn("Column5", typeof(string)));
dt.Columns.Add(new DataColumn("Column6", typeof(string)));
dt.Columns.Add(new DataColumn("Column7", typeof(string)));
dt.Columns.Add(new DataColumn("Column8", typeof(string)));
dt.Columns.Add(new DataColumn("Column9", typeof(string)));
//Add a Dummy Data on Initial Load
dr = dt.NewRow();
dt.Rows.Add(dr);
//Store the DataTable in ViewState
ViewState["CurrentTable"] = dt;
//Bind the DataTable to the Grid
gvIlmpCourse.DataSource = dt;
gvIlmpCourse.DataBind();
gvIlmpCourse.Rows[0].Cells[0].Width = 40;
// AddNewRowToGrid();
DataTable dt1 = new DataTable();
DataRow dr1 = null;
dt1.Columns.Add(new DataColumn("Column1", typeof(string)));
//Add a Dummy Data on Initial Load
dr1 = dt1.NewRow();
dt1.Rows.Add(dr1);
//Store the DataTable in ViewState
ViewState["CurrentTableWorkshop"] = dt1;
//Bind the DataTable to the Grid
gvWorkshop.DataSource = dt1;
gvWorkshop.DataBind();
foreach (ILMPCourseVO ilmpCourseVO in ilmpVO.IlmpCourses)
{
AddNewRowToCourseGrid(ilmpCourseVO);
}
List <WorkshopVO> workshops = ilmpBO.GetILMPWorkshopForId(ilmpId);
foreach (WorkshopVO workshopVO in workshops)
{
AddNewRowToWorkshopGrid(workshopVO.WorkshopId);
}
/* foreach (TemplateCourseVO templateCourseVO in ilmpTemplateVO.TemplateCourses)
* {
* if (templateCourseVO.WorkshopId == 0 && templateCourseVO.CourseCode != "")
* {
* AddNewRowToCourseGrid(templateCourseVO);
* }
* else
* {
* AddNewRowToWorkshopGrid(templateCourseVO.WorkshopId);
* }
* }*/
if ((gvIlmpCourse.Rows[gvIlmpCourse.Rows.Count - 1].Cells[0].FindControl("lblCourseCode") as Label).Text == "")
{
gvIlmpCourse.Rows[gvIlmpCourse.Rows.Count - 1].Visible = false;
}
if ((gvWorkshop.Rows[gvWorkshop.Rows.Count - 1].Cells[0].FindControl("lblWorkshop") as Label).Text == "")
{
gvWorkshop.Rows[gvWorkshop.Rows.Count - 1].Visible = false;
}
}
List <ILMPCourseGridVO> ilmpCourses = ilmpBO.GetILMPCoursesForId(ilmpId);
for (int i = 0; i < gvIlmpCourse.Rows.Count - 1; i++)
{
((DropDownList)gvIlmpCourse.Rows[i].Cells[2].FindControl("ddSemester")).Text = ilmpCourses[i].Semester.ToString();
((DropDownList)gvIlmpCourse.Rows[i].Cells[3].FindControl("ddYear")).Text = ilmpCourses[i].Year.ToString();
((DropDownList)gvIlmpCourse.Rows[i].Cells[1].FindControl("ddCourseType")).Text = ilmpCourses[i].CourseType.ToString();
((DropDownList)gvIlmpCourse.Rows[i].Cells[1].FindControl("ddResult")).Text = ilmpCourses[i].Result.ToString();
}
}
protected void btnUpdateStudent_Click(object sender, EventArgs e)
{
try
{
int studentID = Convert.ToInt32(txtStudentID.Text.Trim());
string firstName = txtFirstName.Text.Trim();
string lastName = txtLastName.Text.Trim();
string programmeId = ddlStudentProgramme.SelectedValue.Trim();
string emailID = txtEmailID.Text.Trim();
string status = ddlStatus.Text.Trim();
DateTime updatedDTM = DateTime.Now;
string active = "";
if (ddlStatus.Text != "Studying")
{
active = "No";
}
else
{
active = "Yes";
}
if (!txtEmailID.Text.Trim().EndsWith("@ess.ais.ac.nz"))
{
Response.Write("<script>alert('Please enter valid email ess.ais.ac.nz');</script>");
}
StudentVO studentVO = new StudentVO(studentID, firstName, lastName, status, updatedDTM);
UserVO userVO = new UserVO(studentID, emailID, active);
if (active == "No" || status == "Withdrawn" || status == "Graduated" || status == "Postponed")
{
ILMPBO ilmpBO = new ILMPBO();
bool ilmpUpdationStatus = ilmpBO.UpdateILMPStatusForStudent(studentID, "No");
}
if (txtFirstName.Text == "" || txtLastName.Text == "" || txtEmailID.Text == "" || lbMajor.Items.Count == 0)
{
Response.Write("<script>alert('Please check First Name, Last Name, Email and Major is filled');</script>");
}
else if (studentBO.UpdateStudent(studentVO) && userBO.UpdateUser(userVO))
{
DBConnection.conn.Open();
string query = "DELETE FROM StudentMajor WHERE StudentID=" + studentID;
SqlCommand cmd2 = new SqlCommand(query, DBConnection.conn);
cmd2.ExecuteNonQuery();
DBConnection.conn.Close();
for (int i = 0; i < lbMajor.Items.Count; i++)
{
ListItem lmajor = lbMajor.Items[i];
StudentMajorVO studentMajorVO = new StudentMajorVO(studentID, programmeId, lmajor.Value, active);
StudentMajorBO studentMajorBO = new StudentMajorBO();
if (studentMajorBO.AddStudent(studentMajorVO))
{
Response.Write("<script>alert('Student updated successfully');</script>");
}
else
{
Response.Write("<script>alert('Error in updating student major');</script>");
}
}
}
else
{
Response.Write("<script>alert('Student update Fail');</script>");
}
}
catch (CustomException ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
catch (SqlException ex)
{
ExceptionUtility.LogException(ex, "Error Page");
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
Response.Write("<script>alert('Student updation failed');</script>");
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string existingStudent = "";
string failedStudent = "";
bool studentExists = false;
try
{
List <StudentMajorVO> studentPgmVOList = new List <StudentMajorVO>();
List <StudentVO> studentVOList = new List <StudentVO>();
List <UserVO> users = new List <UserVO>();
UserDaoImpl userDaoImpl = new UserDaoImpl();
UserVO userVO = null;
IStudentDao studentDao = new StudentDaoImpl();
List <string> existingStudentIDList = studentDao.GetAllStudentIDAsList();
string filepath = Server.MapPath("~/Files/") + Path.GetFileName(fuBulkCourseUpload.PostedFile.FileName);
if (fuBulkCourseUpload.HasFile)
{
fuBulkCourseUpload.SaveAs(filepath);
string data = File.ReadAllText(filepath);
Boolean headerRowHasBeenSkipped = false;
foreach (string row in data.Split('\n'))
{
if (headerRowHasBeenSkipped)
{
string programme = "";
studentExists = false;
if (!string.IsNullOrEmpty(row))
{
userVO = new UserVO();
StudentVO studentVO = new StudentVO();
StudentMajorVO studentMajorVO = new StudentMajorVO();
List <string> pgmList = new List <string>();
int i = 0;
foreach (string cell in row.Split(','))
{
if (!studentExists)
{
string celltemp = "";
if (cell.Contains("\r"))
{
celltemp = cell.Replace("\r", "");
}
else
{
celltemp = cell;
}
celltemp = celltemp.Trim();
switch (i)
{
case 0:
{
if (existingStudentIDList.Contains(celltemp))
{
existingStudent += celltemp + ",";
studentExists = true;
}
else
{
studentVO.StudentId = Convert.ToInt32(celltemp);
studentMajorVO.studentId = Convert.ToInt32(celltemp);;
userVO.StudentID = Convert.ToInt32(celltemp);
}
break;
}
case 1:
{
studentVO.FirstName = celltemp;
break;
}
case 2:
{
studentVO.LastName = celltemp;
break;
}
case 3:
{
/* if (celltemp.Contains("&"))
* {
* foreach (string pgm in celltemp.Split('&'))
* {
* pgmList.Add(pgm);
* }
* }
* else
* {
* pgmList.Add(celltemp);
* }*/
programme = celltemp;
break;
}
case 4:
{
if (celltemp.Contains("&"))
{
StudentMajorVO sm;
// foreach (string pgm in pgmList)
//{
foreach (string mjr in celltemp.Split('&'))
{
sm = new StudentMajorVO();
sm.StudentId = studentMajorVO.StudentId;
sm.ProgrammeID = programme;
sm.MajorID = mjr.Trim();
studentPgmVOList.Add(sm);
}
// }
}
else
{
StudentMajorVO sm;
// foreach (string pgm in pgmList)
//{
sm = new StudentMajorVO();
sm.StudentId = studentMajorVO.StudentId;
sm.ProgrammeID = programme;
sm.MajorID = celltemp;
studentPgmVOList.Add(sm);
//}
}
break;
}
case 5:
{
if (!celltemp.EndsWith("@ess.ais.ac.nz"))
{
existingStudent += celltemp + ",";
studentExists = true;
}
else
{
userVO.EmailID = celltemp;
}
break;
}
}
}
i++;
}
if (!studentExists)
{
studentVO.Program = studentPgmVOList;
users.Add(userVO);
studentVOList.Add(studentVO);
}
}
}
headerRowHasBeenSkipped = true;
}
if (studentVOList.Count > 0)
{
foreach (StudentVO studentVO in studentVOList)
{
studentVO.Status = "Studying";
studentVO.CreatedDate = DateTime.Now;
studentVO.LastUpdatedDate = DateTime.Now;
string status = (studentBO.AddStudent(studentVO)).ToString();
if (status.Contains("fail"))
{
failedStudent += studentVO.StudentID;
}
}
foreach (StudentMajorVO studentMajorVO in studentPgmVOList)
{
studentMajorVO.Active = "Yes";
string status = (studentMajorBO.AddStudent(studentMajorVO)).ToString();
if (status.Contains("fail"))
{
failedStudent += studentMajorVO.StudentId;
}
}
userDaoImpl.AddStudentUsers(users);
string usermessage = " Students upload completed.";
if (failedStudent != "")
{
usermessage += " FailedStudentList " + failedStudent;
}
if (existingStudent != "")
{
usermessage += " Skipped existing student. " + existingStudent;
}
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + usermessage + "');", true);
}
}
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
}