public bool OverRideCourse(int advisorID, StudentCourse sc)
{
dlStudent = new DLStudent();
return dlStudent.OverRideCourse(advisorID, sc);
}
public List<StudentCourse> GetAvailableCourses(List<StudentCourse> studentCourses)
{
SqlConnection connection = SqlHelper.CreateConnection();
List<StudentCourse> availableCourses = new List<StudentCourse>();
string RegisteredCoursesList = "-";
foreach (StudentCourse studentCourse in studentCourses)
{
RegisteredCoursesList = RegisteredCoursesList + "," + studentCourse.CourseID+"-"+ studentCourse.SemesterID +"-"+studentCourse.Status;
}
RegisteredCoursesList = RegisteredCoursesList.Replace("-,", "");
if (RegisteredCoursesList.Length == 1) {
RegisteredCoursesList = "";
}
try
{
SqlParameter pCourseList = new SqlParameter("@CourseIDs", SqlDbType.NVarChar);
SqlParameter pSemester = new SqlParameter("@SemesterID", SqlDbType.Int);
string spName = "GetAvailableCoursesForStudentCheckingAllPreReqChain";
//Replace 2 with current semester
for (int sem = 1; sem <= 12; sem++)
{
pCourseList.Value = RegisteredCoursesList;
//RegisteredCoursesList;
pSemester.Value = sem;
using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, pCourseList, pSemester))
{
while (dr.Read())
{
StudentCourse st = new StudentCourse();
st.CourseID = SqlHelper.ToInt32(dr["CourseID"]);
st.Credits = SqlHelper.ToInt32(dr["Credits"]);
st.SemesterCourseID = SqlHelper.ToInt32(dr["SemCourseID"]);
st.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]);
st.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]);
st.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]);
st.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]);
st.CourseName = SqlHelper.ToString(dr["CourseName"]);
st.Status = "Not Yet";
st.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]);
st.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]);
st.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]);
st.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]);
availableCourses.Add(st);
}
}
}
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("SearchStudent: " + sqlEx.ToString());
}
catch (Exception Ex)
{
SqlHelper.CloseConnection(connection);
throw new Exception("SearchStudent: " + Ex.ToString());
}
return availableCourses;
}
public List<StudentCourse> OverRideCourses(int studentID , int SemesterID)
{
SqlConnection connection = SqlHelper.CreateConnection();
StringBuilder sb = new StringBuilder();
List<StudentCourse> studentCourses = new List<StudentCourse>();
try
{
sb.Append("SELECT SC.ID AS SemesterCourseID,C.ID AS CourseID,C.Name AS CourseName,SC.SemesterID AS SemesterID ,C.Credits AS Credits, ");
sb.Append("IsElectiveAFL,IsElectiveBFL FROM SemesterCourse SC INNER JOIN Course C ON SC.CourseID = C.ID WHERE C.ID NOT IN");
sb.Append("(SELECT CourseID FROM StudentCourse WHERE StudentID = "+studentID);
sb.Append(") AND SemesterID ="+SemesterID);
using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.Text, sb.ToString()))
{
while (dr.Read())
{
StudentCourse sc = new StudentCourse();
sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]);
sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]);
sc.CourseName = SqlHelper.ToString(dr["CourseName"]);
sc.Credits = SqlHelper.ToInt32(dr["Credits"]);
sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]);
sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]);
sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]);
studentCourses.Add(sc);
}
}
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + sqlEx.ToString());
}
catch (Exception e)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + e.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return studentCourses;
}
public bool OverRideCourse(int advisorID, StudentCourse sc)
{
SqlConnection connection = SqlHelper.CreateConnection();
StringBuilder sb = new StringBuilder();
try
{
//Creating SqlParameter objects to fields in student
sb.Append("INSERT INTO StudentCourse(StudentID,CourseID,SemesterCourseID,CourseName,ElectiveID,SemesterID,");
sb.Append("Credits,[Status],IsActiveFL,CreationDate,LastUpdatedDate,CreatedBy,LastUpdatedBy) VALUES");
sb.Append("(" + sc.StudentID + "," + sc.CourseID + ","+sc.SemesterCourseID+",'" + sc.CourseName + "'");
sb.Append(","+sc.ElectiveID+"," +sc.SemesterID + "," + sc.Credits + ",'Pending','" + sc.IsActiveFL);
sb.Append("','" + DateTime.UtcNow + "','" + DateTime.UtcNow + "'," + advisorID + "," + advisorID);
sb.Append(")");
SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sb.ToString());
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + sqlEx.ToString());
}
catch (Exception e)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + e.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return true;
}
public List<StudentCourse> GetTransferedCourses(int StudentID)
{
SqlConnection connection = SqlHelper.CreateConnection();
StringBuilder sb = new StringBuilder();
List<StudentCourse> studentCourses = new List<StudentCourse>();
try
{
//Creating SqlParameter objects to fields in student
sb.Append("SELECT cour.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID,cour.Name as CourseName,");
sb.Append("cour.ID as CourseID,cour.Credits as Credits,cour.IsElectiveAFL as IsElectiveAFL ,cour.IsElectiveBFL as IsElectiveBFL ,");
sb.Append("sc.SemesterID as SemesterID,sc.[Status] as Status,sc.IsActiveFL as IsActiveFL,sc.CreationDate as CreationDate,");
sb.Append("sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc ");
sb.Append(" JOIN Course cour ON cour.Name = sc.CourseName WHERE StudentID = " + StudentID + " AND SemesterID =0 ;");
using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.Text, sb.ToString()))
{
while (dr.Read())
{
StudentCourse sc = new StudentCourse();
sc.ID = SqlHelper.ToInt32(dr["ID"]);
sc.StudentID = SqlHelper.ToInt32(dr["StudentID"]);
sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]);
sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]);
sc.CourseName = SqlHelper.ToString(dr["CourseName"]);
sc.Credits = SqlHelper.ToInt32(dr["Credits"]);
sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]);
sc.Status = SqlHelper.ToString(dr["Status"]);
sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]);
sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]);
sc.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]);
sc.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]);
sc.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]);
sc.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]);
sc.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]);
studentCourses.Add(sc);
}
}
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + sqlEx.ToString());
}
catch (Exception e)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentDetails: " + e.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return studentCourses;
}
public List<StudentCourse> GetStudentRegisteredCourses(int studentID)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb1 = new StringBuilder();
SqlConnection connection = SqlHelper.CreateConnection();
try
{
sb.Append("SELECT cour.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID,cour.Name as CourseName,cour.ID as CourseID,");
sb.Append("cour.Credits as Credits,cour.IsElectiveAFL as IsElectiveAFL ,cour.IsElectiveBFL as IsElectiveBFL ,sc.SemesterID as SemesterID,sc.[Status] as Status,");
sb.Append("sc.IsActiveFL as IsActiveFL,sc.CreationDate as CreationDate,sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,");
sb.Append("sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc INNER JOIN SemesterCourse SemCour ON sc.SemesterCourseID=SemCour.ID ");
sb.Append(" JOIN Course cour ON cour.ID = SemCour.CourseID WHERE StudentID = " + studentID + "; ");
sb1.Append("SELECT sc.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID ,CourseName,CourseID,Credits,");
sb1.Append("ElectiveID,SemesterID,[Status],IsActiveFL,CreationDate,sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,");
sb1.Append("sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc WHERE (CourseID =0 or SemesterID=0) and StudentID = " + studentID + "; ");
using(SqlDataReader dr = SqlHelper.ExecuteReader(connection,CommandType.Text,sb.ToString()))
{
while (dr.Read())
{
StudentCourse sc = new StudentCourse();
sc.ID = SqlHelper.ToInt32(dr["ID"]);
sc.StudentID = SqlHelper.ToInt32(dr["StudentID"]);
sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]);
sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]);
sc.CourseName = SqlHelper.ToString(dr["CourseName"]);
sc.Credits = SqlHelper.ToInt32(dr["Credits"]);
sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]);
sc.Status = SqlHelper.ToString(dr["Status"]);
sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]);
sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]);
sc.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]);
sc.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]);
sc.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]);
sc.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]);
sc.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]);
studentCourses.Add(sc);
}
}
using (SqlDataReader dr1 = SqlHelper.ExecuteReader(connection, CommandType.Text, sb1.ToString()))
{
while (dr1.Read())
{
StudentCourse sc = new StudentCourse();
sc.ID = SqlHelper.ToInt32(dr1["ID"]);
sc.StudentID = SqlHelper.ToInt32(dr1["StudentID"]);
sc.SemesterCourseID = SqlHelper.ToInt32(dr1["SemesterCourseID"]);
sc.CourseID = SqlHelper.ToInt32(dr1["CourseID"]);
sc.CourseName = SqlHelper.ToString(dr1["CourseName"]);
sc.Credits = SqlHelper.ToInt32(dr1["Credits"]);
sc.SemesterID = SqlHelper.ToInt32(dr1["SemesterID"]);
sc.Status = SqlHelper.ToString(dr1["Status"]);
sc.ElectiveID = SqlHelper.ToInt32(dr1["ElectiveID"]);
if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 1)
{
sc.IsElectiveAFL = true;
}
else if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 2)
{
sc.IsElectiveBFL = true;
}
else if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 3)
{
sc.GenEd = true;
}
sc.IsActiveFL = SqlHelper.ToBool(dr1["IsActiveFL"]);
sc.CreationDate = SqlHelper.ToDateTime(dr1["CreationDate"]);
sc.LastUpdatedDate = SqlHelper.ToDateTime(dr1["LastUpdatedDate"]);
sc.CreatedBy = SqlHelper.ToInt32(dr1["CreatedBy"]);
sc.LastUpdatedBy = SqlHelper.ToInt32(dr1["LastUpdatedBy"]);
studentCourses.Add(sc);
}
}
}
catch (SqlException sqlEx)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentSemesterCourses: " + sqlEx.ToString());
}
catch (Exception ex)
{
SqlHelper.CloseConnection(connection);
throw new Exception("GetStudentSemesterCourses: " + ex.ToString());
}
finally
{
SqlHelper.CloseConnection(connection);
}
return studentCourses;
}