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;
        }