/// <summary>
/// Gets the List of Instructors of a particular Term where a Subject is taken in a building
/// </summary>
/// <param name="term">Term</param>
/// <param name="subject">Subject</param>
/// <param name="Building">Building</param>
/// <returns> List of Instructors</returns>
public List <Instructor> getInstructor(Term term, Subject subject, Building building)
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Instructor> lstInstructors = new List <Instructor>();
String query = "SELECT distinct Instructors.firstName, Instructors.lastName FROM Instructors INNER JOIN " +
"Sections ON Instructors.instructorId = Sections.instructorId INNER JOIN " +
"Courses ON Sections.courseId = Courses.courseId INNER JOIN " +
"Subjects ON Courses.subjectId = Subjects.subjectId INNER JOIN " +
"Terms ON Sections.termId = Terms.termId INNER JOIN " +
"SectionLocations ON Sections.sectionId = SectionLocations.sectionId INNER JOIN " +
"Buildings ON SectionLocations.buildingId = Buildings.buildingId " +
"WHERE (Terms.quarter = '" + term.Quarter + "') AND " +
"(Subjects.subject = '" + subject.SubjectName + "') AND (Terms.year =" + term.Year + ") " +
" AND (Buildings.Building ='" + building.BuildingName + "')";
SQLiteDataReader dr = DataAccessEntity1.ExecReader(query);
if (dr.HasRows)
{
while (dr.Read())
{
lstInstructors.Add(new Instructor(dr.GetString(0), dr.GetString(1)));
}
}
return(lstInstructors);
}
public List <string> getRooms(Term term, string location)
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <string> lstrooms = new List <string>();
String query = "SELECT distinct SectionLocations.roomNum FROM Buildings INNER JOIN " +
"SectionLocations ON Buildings.buildingId = SectionLocations.buildingId INNER JOIN " +
"Sections ON SectionLocations.sectionId = Sections.sectionId INNER JOIN " +
"Courses ON Sections.courseId = Courses.courseId INNER JOIN " +
"Subjects ON Courses.subjectId = Subjects.subjectId INNER JOIN " +
"Terms ON Sections.termId = Terms.termId " +
"WHERE (Terms.quarter = '" + term.Quarter + "') AND (Terms.year =" + term.Year + ") " +
"and Buildings.building='" + location + "' " +
"AND SectionLocations.roomNum<>'' ";
SQLiteDataReader dr = DataAccessEntity1.ExecReader(query);
if (dr.HasRows)
{
while (dr.Read())
{
lstrooms.Add((dr.GetString(0)));
}
}
return(lstrooms);
}
/// <summary>
/// Gets the List of Locations of a particular Term where certain Subject is taken
/// </summary>
/// <param name="term">Term</param>
/// <param name="subject">Subject</param>
/// <returns> List of Locations</returns>
public List <Building> getLocations(Term term, Subject subject)
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Building> lstBuildings = new List <Building>();
String query = "SELECT distinct Buildings.building FROM Buildings INNER JOIN " +
"SectionLocations ON Buildings.buildingId = SectionLocations.buildingId INNER JOIN " +
"Sections ON SectionLocations.sectionId = Sections.sectionId INNER JOIN " +
"Courses ON Sections.courseId = Courses.courseId INNER JOIN " +
"Subjects ON Courses.subjectId = Subjects.subjectId INNER JOIN " +
"Terms ON Sections.termId = Terms.termId " +
"WHERE (Terms.quarter = '" + term.Quarter + "') AND (Terms.year =" + term.Year + ")";
if (subject != null)
{
query += " AND (Subjects.subject = '" + subject.SubjectName + "') ";
}
SQLiteDataReader dr = DataAccessEntity1.ExecReader(query);
if (dr.HasRows)
{
while (dr.Read())
{
lstBuildings.Add(new Building(dr.GetString(0)));
}
}
return(lstBuildings);
}
public List <Building> getLocations()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Building> lstBuildings = new List <Building>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT building FROM Buildings");
if (dr.HasRows)
{
while (dr.Read())
{
lstBuildings.Add(new Building(dr.GetString(0)));
}
}
return(lstBuildings);
}
public List <Subject> getSubjects()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Subject> lstSubjects = new List <Subject>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT subject FROM Subjects");
if (dr.HasRows)
{
while (dr.Read())
{
lstSubjects.Add(new Subject(dr.GetString(0)));
}
}
return(lstSubjects);
}
public List <Schedule> getSchedule()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Schedule> lstSchedules = new List <Schedule>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT scheduleTypeId, scheduleType FROM ScheduleTypes");
if (dr.HasRows)
{
while (dr.Read())
{
lstSchedules.Add(new Schedule(dr.GetInt16(0), dr.GetString(1)));
}
}
return(lstSchedules);
}
public List <Term> getTerms()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Term> lstterms = new List <Term>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT DISTINCT termId, quarter, year FROM Terms");
if (dr.HasRows)
{
while (dr.Read())
{
lstterms.Add(new Term(dr.GetString(1), Int32.Parse(dr.GetString(2))));
}
}
return(lstterms);
}
public List <Instructor> getInstructors()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Instructor> lstInstructors = new List <Instructor>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT firstName, lastName FROM Instructors");
if (dr.HasRows)
{
while (dr.Read())
{
lstInstructors.Add(new Instructor(dr.GetString(0), dr.GetString(1)));
}
}
return(lstInstructors);
}
/// <summary>
/// Gets the List of Terms
/// </summary>
/// <returns>List of Terms </returns>
public List <Term> getTerms()
{
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Term> lstterms = new List <Term>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader("SELECT distinct quarter, year FROM Terms");
if (dr.HasRows)
{
while (dr.Read())
{
lstterms.Add(new Term(dr.GetString(0), dr.GetInt32(1)));
}
}
return(lstterms);
}
public List <Subject> getSubjectsbyTerm(Term term)
{
String query = "SELECT Subjects.subject FROM Courses INNER JOIN " +
"Sections ON Courses.courseId = Sections.courseId INNER JOIN " +
"Subjects ON Courses.subjectId = Subjects.subjectId INNER JOIN " +
"Terms term ON Sections.termId = term.termId " +
"WHERE (term.quarter = '" + term.Quarter + "') AND (term.year =" + term.Year + ")";
DataAccessEntity DataAccessEntity1 = new DataAccessEntity();
List <Subject> lstSubjects = new List <Subject>();
SQLiteDataReader dr = DataAccessEntity1.ExecReader(query);
if (dr.HasRows)
{
while (dr.Read())
{
lstSubjects.Add(new Subject(dr.GetString(0)));
}
}
return(lstSubjects);
}