public DataSet findClasses(Building building, string roomNum, string Days, string startTime, string startTime1) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = "SELECT B.building AS Building, SL.roomNum AS Room, ST.day AS Days, " + " ST.timeStart, ST.timeEnd AS Times, " + "I.firstName, I.lastName, C.courseNum, S.subject, STP.scheduleType" + " FROM Sections SC INNER JOIN" + " Courses C ON SC.courseId = C.courseId INNER JOIN" + " SectionTimes ST ON SC.sectionId = ST.sectionId INNER JOIN" + " Subjects S ON C.subjectId = S.subjectId INNER JOIN" + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId INNER JOIN" + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN" + " Buildings B ON B.buildingId = SL.buildingId INNER JOIN" + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN" + " Terms T ON SC.termId = T.termId " + "WHERE T.year=" + DateTime.Now.Year + " and B.building = '" + building.BuildingName + "' " + "or sl.roomNum LIKE '%" + roomNum + "%' "; if (!Days.Equals("")) { query += " AND ST.DAY IN(" + Days + ") "; } if (!startTime.Equals("")) { query += "AND ST.TIMESTART >= '" + startTime + "' AND ST.TIMEEND <= '" + startTime1 + "' "; } return(DataAccessEntity1.Execute(query)); }
public DataSet findCoursesbyKeyword(Term term, Instructor instructor, string keyword, List <string> Days, string startTime, string startTime1) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = " SELECT DISTINCT SC.CRN, S.subject AS [Subj.], C.courseNum AS [#], SC.sectionId AS Section, " + " C.credits AS [Cr Hrs], SC.[enrlCap] || '/' || SC.enrlAct AS [EnrollmentTaken/Avail], " + " SC.waitCap || '/' || SC.waitAct AS [WaitlistTaken/Avail], SC.title AS Title, SC.[meetingStart] || '-' ||SC.meetingEnd AS [Section Meeting Dates], " + " STP.scheduleType AS Type, coalescE(ST.day, '-') AS Days , ST.timeStart || '-' || ST.timeEnd AS Times, B.building || '-' || SL.roomNum AS [Building - Room], " + " case WHEN (I.firstName) is null then 'To Be Announced' ELSE ( I.firstName || ' ' || I.lastName) END AS [Instructor], " + " SC.TEXTBOOKLINK AS [View Books], SC.notes AS [Important comments about the section] " + " FROM Sections SC INNER JOIN" + " Courses C ON SC.courseId = C.courseId inner join" + " SectionTimes ST ON SC.sectionId = ST.sectionId LEFT OUTER JOIN" + " Subjects S ON C.subjectId = S.subjectId LEFT OUTER JOIN" + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId LEFT OUTER JOIN" + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN" + " Buildings B ON B.buildingId = SL.buildingId LEFT OUTER JOIN" + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN" + " Terms T ON SC.termId = T.termId " + " WHERE (T.quarter = '" + term.Quarter + "' AND T.[YEAR]='" + term.Year + "') " + " and (S.SUBJECT like '%" + keyword + "%' " + " or B.building like '%" + keyword + "%' " + " or I.FIRSTNAME like '%" + instructor.FirstName + "%' or I.LASTNAME like '%" + instructor.LastName + "%' " + " or C.courseNum like '%" + keyword + "%' or stP.SCHEDULETYPE like '%" + keyword + "%' " + " or SC.TITLE LIKE '%" + keyword + "%' " + " or sl.roomNum LIKE '%" + keyword + "%' )"; if (Days.Count > 0) { foreach (string day in Days) { query += " AND ST.DAY LIKE '%" + day + "%'"; } } if (!startTime.Equals("")) { if (startTime.Substring(6, 2).Equals(startTime1.Substring(6, 2))) { operation = " AND "; } else { operation = " OR "; } query += "AND ((substr(trim(ST.TIMESTART),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) >= '" + startTime.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMESTART),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) <= '" + startTime1.Substring(6, 2) + "') ) "; query += "AND ((substr(trim(ST.TIMEEND),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) <= '" + startTime1.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMEEND),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) >= '" + startTime.Substring(6, 2) + "')) "; } return(DataAccessEntity1.Execute(query)); }
public DataSet findCoursesbyInstructor(Term term, Instructor instructor, string Days, string startTime, string startTime1) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = "SELECT C.CRN, S.subject AS [Subj.], C.courseNum AS [#], SC.sectionId AS Section, " + "C.credits AS [Cr Hrs], SC.[enrlCap ] + '/' + SC.enrlAct AS [EnrollmentTaken/Avail], " + "SC.waitCap || '/' || SC.waitAct AS [WaitlistTaken/Avail], SC.title AS Title, SC.[meetingStart ] || '-' ||SC.meetingEnd AS [Section Meeting Dates], " + "STP.scheduleType AS Type, coalescE(ST.day, '-') AS Days , ST.timeStart || '-' || ST.timeEnd AS Times, B.building || '-' || SL.roomNum AS [Building - Room], " + " case WHEN (I.firstName) is null then 'To Be Announced' ELSE ( I.firstName || ' ' || I.lastName) END AS [Instructor], " + " SC.notes AS [Important comments about the section] " + " FROM Sections SC INNER JOIN" + " Courses C ON SC.courseId = C.courseId inner JOIN" + " SectionTimes ST ON SC.sectionId = ST.sectionId INNER JOIN" + " Subjects S ON C.subjectId = S.subjectId INNER JOIN" + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId INNER JOIN" + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN" + " Buildings B ON B.buildingId = SL.buildingId LEFT OUTER JOIN" + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN" + " Terms T ON SC.termId = T.termId " + "WHERE T.quarter = '" + term.Quarter + "' AND T.[YEAR]='" + term.Year + "' "; if (instructor != null) { query += " AND I.FIRSTNAME='" + instructor.FirstName + "' AND I.LASTNAME='" + instructor.LastName + "' "; } //if (!CourseStatus.Equals("")) // query += "AND stP.SCHEDULETYPE ='" + CourseStatus + "' "; if (!Days.Equals("")) { query += " AND ST.DAY IN(" + Days + ") "; } if (!startTime.Equals("")) { query += "AND ST.TIMESTART >= '" + startTime + "' AND ST.TIMEEND <= '" + startTime1 + "' "; } return(DataAccessEntity1.Execute(query)); }
public DataSet findClasses(Term term, Building building, string roomNum, List <string> Days, string startTime, string startTime1) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = " SELECT DISTINCT [Section Meeting Dates], Times, (CASE WHEN ps.day = 'M' THEN ps.Info END) AS Monday, (CASE WHEN ps.day = 'T' THEN ps.Info END) AS Tuesday, " + "(CASE WHEN ps.day = 'W' THEN ps.Info END) AS Wednesday, (CASE WHEN ps.day = 'R' THEN ps.Info END) AS Thursday, " + " (CASE WHEN ps.day = 'F' THEN ps.Info END) AS Friday, (CASE WHEN ps.day = 'S' THEN ps.Info END) AS Saturday, " + " (CASE WHEN ps.day = 'U' THEN ps.Info END) AS Sunday " + " FROM (SELECT distinct sc.meetingstart, sc.meetingend, ST.day, " + " ('Course No:'|| C.courseNum ||' Instructor:' || case WHEN (I.firstName) is null then 'To Be Announced' ELSE ( I.firstName || ' ' || I.lastName) END) AS [Info] , " + " ST.timeStart || '-' || ST.timeEnd AS Times ,SC.[meetingStart] || '-' ||SC.meetingEnd AS [Section Meeting Dates]" + " FROM Sections SC INNER JOIN " + " Courses C ON SC.courseId = C.courseId inner JOIN " + " SectionTimes ST ON SC.sectionId = ST.sectionId INNER JOIN " + " Subjects S ON C.subjectId = S.subjectId INNER JOIN " + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId INNER JOIN " + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN " + " Buildings B ON B.buildingId = SL.buildingId INNER join " + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN " + " Terms T ON SC.termId = T.termId " + " WHERE T.quarter = '" + term.Quarter + "' AND T.[YEAR]='" + term.Year + "' " + " and B.building = '" + building.BuildingName + "' "; if (!roomNum.Equals("")) { query += " AND sl.roomNum ='" + roomNum + "' "; } if (Days.Count > 0) { foreach (string day in Days) { query += " AND ST.DAY LIKE '%" + day + "%'"; } } if (!startTime.Equals("")) { if (startTime.Substring(6, 2).Equals(startTime1.Substring(6, 2))) { operation = " AND "; } else { operation = " OR "; } query += "AND ((substr(trim(ST.TIMESTART),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) >= '" + startTime.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMESTART),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) <= '" + startTime1.Substring(6, 2) + "') ) "; query += "AND ((substr(trim(ST.TIMEEND),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) <= '" + startTime1.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMEEND),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) >= '" + startTime.Substring(6, 2) + "')) "; } query += ") ps ORDER BY [Section Meeting Dates], Times "; return(DataAccessEntity1.Execute(query)); }
public DataSet findCourses(Term term, List <Subject> subjects, string courseOperator, string CourseNo, Building building, Instructor instructor, string creditHrs, bool exclude, bool distanceLearning, bool creditcoursesprovided, bool courseOfferings) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = " SELECT DISTINCT SC.CRN, S.subject AS [Subj.], C.courseNum AS [#], SC.sectionId AS Section, " + " C.credits AS [Cr Hrs], SC.[enrlCap] || '/' || SC.enrlAct AS [EnrollmentTaken/Avail], " + " SC.waitCap || '/' || SC.waitAct AS [WaitlistTaken/Avail], SC.title AS Title, SC.[meetingStart] || '-' ||SC.meetingEnd AS [Section Meeting Dates], " + " STP.scheduleType AS Type, coalescE(ST.day, '-') AS Days, ST.timeStart || '-' || ST.timeEnd AS Times, B.building || '-' || SL.roomNum AS [Building - Room], " + " case WHEN (I.firstName) is null then 'To Be Announced' ELSE ( I.firstName || ' ' || I.lastName) END AS [Instructor], " + "SC.TEXTBOOKLINK AS [View Books], SC.notes AS [Important comments about the section] " + " FROM Sections SC INNER JOIN" + " Courses C ON SC.courseId = C.courseId LEFT OUTER JOIN" + " SectionTimes ST ON SC.sectionId = ST.sectionId INNER JOIN" + " Subjects S ON C.subjectId = S.subjectId INNER JOIN" + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId INNER JOIN" + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN" + " Buildings B ON B.buildingId = SL.buildingId LEFT OUTER JOIN" + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN" + " Terms T ON SC.termId = T.termId " + " WHERE T.quarter = '" + term.Quarter + "' AND T.[YEAR]='" + term.Year + "' "; if (subjects != null) { query += " AND S.SUBJECT in("; foreach (Subject subject in subjects) { query += "'" + subject.SubjectName + "',"; } query = query.TrimEnd(','); query += ") "; } if (!building.BuildingName.Equals("ALL")) { query += " AND B.building='" + building.BuildingName + "' "; } if (!instructor.FirstName.Equals("ALL")) { query += " AND I.FIRSTNAME='" + instructor.FirstName + "' AND I.LASTNAME='" + instructor.LastName + "' "; } if (!CourseNo.Equals("ALL")) { switch (courseOperator.Substring(0, 1)) { case "e": query += " AND C.courseNum = " + CourseNo + " "; break; case "g": query += " AND C.courseNum >=" + CourseNo + " "; break; case "l": query += " AND C.courseNum <=" + CourseNo + " "; break; } } if (exclude) { query += " AND sc.isCanceled =0 "; } if (distanceLearning) { query += " AND sc.isOnline =1 "; } if (courseOfferings) { query += " AND sc.isEXL =1 "; } if (creditcoursesprovided) { query += " AND sc.isETIE =1 "; } if (!creditHrs.Equals("")) { query += " AND C.credits = " + creditHrs; } return(DataAccessEntity1.Execute(query)); }
public DataSet findCourses(Term term, Subject subject, Building building, Instructor instructor, string CourseNo, string CourseType, string CourseStatus, List <string> Days, string title, string startTime, string startTime1, string CreditHrs1, string CreditHrs2) { DataAccessEntity DataAccessEntity1 = new DataAccessEntity(); string query = " SELECT DISTINCT SC.CRN, S.subject AS [Subj.], C.courseNum AS [#], SC.sectionId AS Section, " + " C.credits AS [Cr Hrs], SC.[enrlCap] || '/' || SC.enrlAct AS [EnrollmentTaken/Avail], " + " SC.waitCap || '/' || SC.waitAct AS [WaitlistTaken/Avail], SC.title AS Title, SC.[meetingStart] || '-' ||SC.meetingEnd AS [Section Meeting Dates], " + " STP.scheduleType AS Type, coalescE(ST.day, '-') AS Days , ST.timeStart || '-' || ST.timeEnd AS Times, B.building || '-' || SL.roomNum AS [Building - Room], " + " case WHEN (I.firstName) is null then 'To Be Announced' ELSE ( I.firstName || ' ' || I.lastName) END AS [Instructor], " + " SC.TEXTBOOKLINK AS [View Books], SC.notes AS [Important comments about the section] " + " FROM Sections SC INNER JOIN" + " Courses C ON SC.courseId = C.courseId inner JOIN" + " SectionTimes ST ON SC.sectionId = ST.sectionId INNER JOIN" + " Subjects S ON C.subjectId = S.subjectId INNER JOIN" + " ScheduleTypes STP ON SC.scheduleTypeId = STP.scheduleTypeId INNER JOIN" + " SectionLocations SL ON SC.sectionId = SL.sectionId INNER JOIN" + " Buildings B ON B.buildingId = SL.buildingId LEFT OUTER JOIN" + " Instructors I ON SC.instructorId = I.instructorId INNER JOIN" + " Terms T ON SC.termId = T.termId " + " WHERE T.quarter = '" + term.Quarter + "' AND T.[YEAR]='" + term.Year + "' "; if (subject != null) { query += " AND S.SUBJECT='" + subject.SubjectName + "'"; } if (building != null) { query += " AND B.building='" + building.BuildingName + "' "; } if (instructor != null) { query += " AND I.FIRSTNAME='" + instructor.FirstName + "' AND I.LASTNAME='" + instructor.LastName + "' "; } if (!CourseNo.Equals("")) { query += " AND C.courseNum like '%" + CourseNo + "%' "; } if (!CourseType.Equals("")) { query += " AND stP.SCHEDULETYPE ='" + CourseType + "' "; } if (Days.Count > 0) { foreach (string day in Days) { query += " AND ST.DAY LIKE '%" + day + "%'"; } } if (!title.Equals("")) { query += " AND SC.TITLE LIKE '%" + title + "%' "; } switch (CourseStatus) { case "Open": query += "AND SC.isCanceled=0 "; break; case "Cancelled and Closed": query += "AND SC.isCanceled=1 "; break; } if (!startTime.Equals("")) { if (startTime.Substring(6, 2).Equals(startTime1.Substring(6, 2))) { operation = " AND "; } else { operation = " OR "; } query += "AND ((substr(trim(ST.TIMESTART),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) >= '" + startTime.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMESTART),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMESTART),7,2) <= '" + startTime1.Substring(6, 2) + "') ) "; query += "AND ((substr(trim(ST.TIMEEND),1,2) <= '" + startTime1.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) <= '" + startTime1.Substring(6, 2) + "') " + operation; query += " (substr(trim(ST.TIMEEND),1,2) >= '" + startTime.Substring(0, 2) + "' "; query += "AND substr(trim(ST.TIMEEND),7,2) >= '" + startTime.Substring(6, 2) + "')) "; } if (!CreditHrs1.Equals("")) { query += "AND C.credits BETWEEN '" + CreditHrs1 + "' AND '" + CreditHrs2 + "' "; } return(DataAccessEntity1.Execute(query)); }