public Student SearchStudentByUser(User user)
{
Access access = new Access();
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@username", user.Username));
string query = "SELECT StudentID, UniversityID, NameAndSurname, Email, Status FROM Student as student, [User] as userb, StudentUser as studentuser WHERE studentuser.U_Username = @username AND student.StudentID = studentuser.U_StudentID AND studentuser.U_Username = userb.Username";
DataTable dt = default(DataTable);
MPPStatus mapperStatus = new MPPStatus();
dt = access.Read(query, parameters);
Student student = new Student();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
student.StudentID = fila["StudentID"].ToString();
student.UniversityID = fila["UniversityID"].ToString();
student.NameAndSurname = fila["NameAndSurname"].ToString();
student.Email = fila["Email"].ToString();
student.Status = mapperStatus.ReturnStatus(fila["Status"].ToString());
}
}
return(student);
}
public List <Subject> ListActiveSubjects()
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
dt = access.Read(QuerySelectActiveSubjects, new List <Parameter>());
List <Subject> subjectList = new List <Subject>();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
Subject subject = new Subject();
subject.SubjectID = Convert.ToInt32(fila["SubjectID"]);
subject.Name = fila["Name"].ToString();
subject.Year = Convert.ToInt32(fila["Year"]);
subject.Status = mapperStatus.ReturnStatus(fila["Status"].ToString());
subject.PeriodType = fila["PeriodType"].ToString();
subject.CorrespondingPeriod = Convert.ToInt32(fila["CorrespondingPeriod"]);
subjectList.Add(subject);
}
}
return(subjectList);
}
public Subject ListSubjectBySubjectID(int SubjectID)
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@SubjectID", SubjectID));
dt = access.Read(QuerySelectSubjectBySubjectID, parameters);
Subject subject = new Subject();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
subject.SubjectID = Convert.ToInt32(fila["SubjectID"]);
subject.Name = fila["Name"].ToString();
subject.Year = Convert.ToInt32(fila["Year"]);
subject.Status = mapperStatus.ReturnStatus(fila["Status"].ToString());
subject.PeriodType = fila["PeriodType"].ToString();
subject.CorrespondingPeriod = Convert.ToInt32(fila["CorrespondingPeriod"]);
}
}
return(subject);
}
public List <Subject> ListUnlockSubjectsBySubject(Subject subject)
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@SubjectID", subject.SubjectID));
dt = access.Read(QuerySelectUnlockSubjectBySubject, parameters);
List <Subject> subjectList = new List <Subject>();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
Subject NewSubject = new Subject();
if (DBNull.Value.Equals(fila["SubjectID"]))
{
return(null);
}
else
{
NewSubject = this.ListSubjectBySubjectID(Convert.ToInt32(fila["SubjectID"]));
}
subjectList.Add(NewSubject);
}
}
return(subjectList);
}
public float GetStudentSubjectAverage(Student student)
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@StudentID", student.StudentID));
dt = access.Read(QuerySelectAverageStudentSubjects, parameters);
float average = 0;
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
average = Convert.ToSingle(fila["Average"]);
}
}
return(average);
}
public List <StudentSubject> ListPendingStudentSubjectsByYear(Student student, string year)
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
int? qualification = null;
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@StudentID", student.StudentID));
dt = access.Read(QuerySelectPendingStudentSubjectsByYear + "'" + year + "'", parameters);
List <StudentSubject> subjectList = new List <StudentSubject>();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
Subject subject = new Subject();
subject.SubjectID = Convert.ToInt32(fila["SubjectID"]);
subject.Name = fila["Name"].ToString();
subject.Year = Convert.ToInt32(fila["Year"]);
if (fila["Qualification"] == DBNull.Value)
{
qualification = null;
}
else
{
qualification = Convert.ToInt32(fila["Qualification"]);
}
StudentSubject information = new StudentSubject(subject, mapperStatus.ReturnStatus(fila["Status"].ToString()), qualification);
subjectList.Add(information);
qualification = -1;
}
}
return(subjectList);
}
public Subject ListApprovedSubjectBySubjectID(Student student, int SubjectID)
{
Access access = new Access();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@SubjectID", SubjectID));
parameters.Add(new Parameter("@StudentID", student.StudentID));
dt = access.Read(QuerySelectApprovedStudentSubject, parameters);
Subject subject = new Subject();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
subject.SubjectID = Convert.ToInt32(fila["SubjectID"]);
subject.Name = fila["Name"].ToString();
}
}
return(subject);
}
public List <Inscription> ListStudentInscriptionHistory(Student student, Status status)
{
Access access = new Access();
MPPSubject mapperSubject = new MPPSubject();
MPPStatus mapperStatus = new MPPStatus();
DataTable dt = default(DataTable);
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@StudentID", student.StudentID));
if (status.status == "Confirmed")
{
dt = access.Read(QuerySelectStudentInscriptionHistory + " AND Status = 'Confirmed'", parameters);
}
else
{
dt = access.Read(QuerySelectStudentInscriptionHistory, parameters);
}
List <Inscription> inscriptions = new List <Inscription>();
if (dt.Rows.Count > 0)
{
foreach (DataRow fila in dt.Rows)
{
Inscription inscription = new Inscription();
inscription.InscriptionID = Convert.ToInt32(fila["InscriptionID"]);
inscription.Subject = mapperSubject.ListSubjectBySubjectID(Convert.ToInt32(fila["SubjectID"]));
inscription.Date = Convert.ToDateTime(fila["Date"].ToString());
inscription.Year = Convert.ToInt32(fila["Year"]);
inscription.CorrespondingPeriod = Convert.ToInt32(fila["CorrespondingPeriod"]);
inscription.Status = mapperStatus.ReturnStatus(fila["Status"].ToString());
inscriptions.Add(inscription);
}
}
return(inscriptions);
}