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