public IList <SurveyStudent> getStudentsBySurveyId(int SurveyId)
{
StringBuilder sql = new StringBuilder();
List <SurveyStudent> students = null;
sql.AppendFormat(
@"Select s.StudentNo,s.LastName,s.FirstName,se.StudentEnrollmentID
From SurveyStudents as ss
inner join Students as s on s.StudentNo=ss.StudentID
inner join StudentEnrollment se on se.StudentEnrollmentID=ss.EnrolmentID
where ss.SurveyID=@surveyId");
SqlCommand cmd = new SqlCommand(sql.ToString());
cmd.Parameters.Add(new SqlParameter("@surveyId", SurveyId));
using (IDataReader reader = this.ExecuteReader(cmd))
{
students = new List <SurveyStudent>();
while (reader.Read())
{
int colIndex = -1;
SurveyStudent ss = new SurveyStudent()
{
StudentNo = reader.GetInt32(++colIndex),
LastName = reader.GetString(++colIndex),
FirstName = reader.GetString(++colIndex),
EnrollmentId = reader.GetInt32(++colIndex)
};
students.Add(ss);
}
}
return(students);
}
public IList <SurveyStudent> getFilteredStudents(int surveyId, int statusId, int programNo, int termId, int groupId, string session)
{
StringBuilder sql = new StringBuilder();
List <SurveyStudent> students = null;
// sql.AppendFormat(
// @"Select s.StudentNo as StudentID,se.StudentEnrollmentID, s.LastName,s.FirstName,t.TermBeginDate, ss.StudentStatus, p.ProgramCode
// from Students as s
// inner join StudentEnrollment as se on s.StudentNo =se.StudentNo
// inner join StudentStatus as ss on ss.StudentStatusID =s.StudentStatusID
// inner join Program as p on p.ProgramNo = s.ProgramNo
// inner join Term as t on t.TermID = se.EnrollmentFirstTermID
// inner join StudentGroup as sg on s.StudentGroupID=sg.GroupID
// inner join Session on Session.Session = s.Session
// where ss.StudentStatusID=@studentStatusId and p.ProgramNo=@programNo and t.TermID=@termId and sg.GroupID=@groupId and Session.Session=@session
// AND CONCAT(s.StudentNo, '-',se.StudentEnrollmentID) NOT IN (
// SELECT CONCAT(StudentID, '-', EnrolmentID)
// FROM SurveyStudents where SurveyID=@surveyId)");
sql.AppendFormat(
@"Select s.StudentNo as StudentID,se.StudentEnrollmentID, s.LastName,s.FirstName,t.TermBeginDate, ss.StudentStatus, p.ProgramCode
from Students as s
inner join StudentEnrollment as se on s.StudentNo =se.StudentNo
inner join StudentStatus as ss on ss.StudentStatusID =s.StudentStatusID
inner join Program as p on p.ProgramNo = s.ProgramNo
inner join Term as t on t.TermID = se.EnrollmentFirstTermID
inner join StudentGroup as sg on s.StudentGroupID=sg.GroupID
inner join Session on Session.Session = s.Session
where ss.StudentStatusID=@studentStatusId and p.ProgramNo=@programNo and t.TermID=@termId and sg.GroupID=@groupId and Session.Session=@session");
SqlCommand cmd = new SqlCommand(sql.ToString());
cmd.Parameters.Add(new SqlParameter("@studentStatusId", statusId));
cmd.Parameters.Add(new SqlParameter("@programNo", programNo));
cmd.Parameters.Add(new SqlParameter("@termId", termId));
cmd.Parameters.Add(new SqlParameter("@groupId", groupId));
cmd.Parameters.Add(new SqlParameter("@session", session));
//cmd.Parameters.Add(new SqlParameter("@surveyId", surveyId));
using (IDataReader reader = this.ExecuteReader(cmd))
{
students = new List <SurveyStudent>();
while (reader.Read())
{
int colIndex = -1;
SurveyStudent ss = new SurveyStudent()
{
StudentNo = reader.GetInt32(++colIndex),
EnrollmentId = reader.GetInt32(++colIndex),
LastName = reader.GetString(++colIndex),
FirstName = reader.GetString(++colIndex),
TermStartDate = reader.GetDateTime(++colIndex),
StudentStatus = reader.GetString(++colIndex),
ProgramCode = reader.GetString(++colIndex)
};
students.Add(ss);
}
}
return(students);
}
