public static List<Course> generateAllRelatedPreRequisites(Course course)
{
List<Course> preRequisiteCourses = new List<Course>();
String queryStatement = @"WITH RECURSIVE
pre_req_courses(n) AS (
VALUES('" + course.id + @"')
UNION
SELECT COURSEID FROM PreRequisite, pre_req_courses
WHERE PreRequisite.FOLLOWID=pre_req_courses.n
)
SELECT COURSEID FROM PreRequisite
WHERE PreRequisite.FOLLOWID IN pre_req_courses";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(queryStatement, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String preReqCourseId = dataReader["COURSEID"].ToString();
String readPreReqCourse = "SELECT * FROM Course WHERE ID = '" + preReqCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readPreReqCourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
Course preReq = null;
while (dataReaderTemp.Read())
{
preReq = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
}
if (preReq != null)
{
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()] PreRequisite Course Information:");
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()]" + "\nID: " + preReq.id + "\nNAME: " + preReq.name + "\nDESC: " + preReq.description);
preRequisiteCourses.Add(preReq);
}
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::generateAllRelatedPreRequisites() " + e.Message);
}
return preRequisiteCourses;
}
public static void fillCourseInfoDataGrid(DataGrid courseInfoDataGrid, Course course)
{
// Display purpose only
//
List<Course> preReqList = DatabaseConnection.getPrerequisiteCourses(course.id);
String preReqString = "";
foreach (Course preReqCourse in preReqList)
{
preReqString += preReqCourse.id + ",";
}
if (preReqString.Length > 1)
{
preReqString = preReqString.Remove(preReqString.Length - 1);
}
List<Course> restrList = DatabaseConnection.getRestrictionCourses(course.id);
String restrString = "";
foreach (Course restrCourse in restrList)
{
restrString += restrCourse.id + ",";
}
if (restrString.Length > 1)
{
restrString = restrString.Remove(restrString.Length - 1);
}
courseInfoDataGrid.Items.Clear();
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Course", description = course.id });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Name", description = course.name });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Description", description = course.description });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Academic Organization", description = course.academicOrg });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Academic Group", description = course.academicGroup });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Course Component", description = course.courseComp });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Grading Basis", description = course.gradingBasis });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Typically Offered", description = course.typeOffered });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Prerequisite(s)", description = preReqString });
courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "Restrictions(s)", description = restrString });
// Workaround to fill the table view
//
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
}
private CommonInternals.CourseType getCourseType(Course targetCourse)
{
CommonInternals.CourseType courseType = CommonInternals.CourseType.ELECTIVE;
if (DatabaseConnection.isCourseCompulsory(targetCourse))
{
courseType = CommonInternals.CourseType.COMPULSORY;
}
else if (DatabaseConnection.isCourseElective(targetCourse))
{
courseType = CommonInternals.CourseType.ELECTIVE;
}
return courseType;
}
private Course getCourseFromCanvas(Course course)
{
Course exsitingCourse = null;
foreach (Course tempCourse in listOfTopCourses)
{
if (tempCourse.id == course.id)
{
exsitingCourse = tempCourse;
break;
}
}
return exsitingCourse;
}
public static void fillCourseInfoDataGrid(DataGrid courseInfoDataGrid, Course course)
{
// Display purpose only
//
List <Course> preReqList = DatabaseConnection.getPrerequisiteCourses(course.id);
String preReqString = "";
foreach (Course preReqCourse in preReqList)
{
preReqString += preReqCourse.id + ",";
}
if (preReqString.Length > 1)
{
preReqString = preReqString.Remove(preReqString.Length - 1);
}
List <Course> restrList = DatabaseConnection.getRestrictionCourses(course.id);
String restrString = "";
foreach (Course restrCourse in restrList)
{
restrString += restrCourse.id + ",";
}
if (restrString.Length > 1)
{
restrString = restrString.Remove(restrString.Length - 1);
}
courseInfoDataGrid.Items.Clear();
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Course", description = course.id
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Name", description = course.name
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Description", description = course.description
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Academic Organization", description = course.academicOrg
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Academic Group", description = course.academicGroup
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Course Component", description = course.courseComp
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Grading Basis", description = course.gradingBasis
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Typically Offered", description = course.typeOffered
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Prerequisite(s)", description = preReqString
});
courseInfoDataGrid.Items.Add(new CourseInfoDataItem()
{
item = "Restrictions(s)", description = restrString
});
// Workaround to fill the table view
//
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
//courseInfoDataGrid.Items.Add(new CourseInfoDataItem() { item = "", description = "" });
}
public static List<Course> readCoursesUsingProgrammePart(Programme programme, String part)
{
List<Course> courses = new List<Course>();
String readCoursesStatement = "SELECT COURSEID FROM CourseProgrammePart WHERE PROGRAMMEID = '" + programme.id + "' AND PART = '" + part + "'";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readCoursesStatement, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String cseCourseId = dataReader["COURSEID"].ToString();
String readCSECourse = "SELECT * FROM Course WHERE ID = '" + cseCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readCSECourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
while (dataReaderTemp.Read())
{
Course cseCourse = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
Logger.Info("[DatabaseConnection::readCoursesUsingProgrammePart()] PreRequisite Course Information:");
Logger.Info("[DatabaseConnection::readCoursesUsingProgrammePart()]" + "\nID: " + cseCourse.id + "\nNAME: " + cseCourse.name + "\nDESC: " + cseCourse.description);
courses.Add(cseCourse);
}
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::readCoursesUsingProgrammePart() " + e.Message);
}
return courses;
}
public static void readCourses(List<Course> courses)
{
String readCourseTable = "SELECT * FROM Course";
courses.Clear();
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readCourseTable, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
Course course = new Course(dataReader["ID"].ToString(),
(int)dataReader["YR"],
(int)dataReader["SEM"],
dataReader["NAME"].ToString(),
dataReader["DESC"].ToString(),
(int)dataReader["POINTS"],
dataReader["ACADEMICORG"].ToString(),
dataReader["ACADEMICGROUP"].ToString(),
dataReader["COURSECOMP"].ToString(),
dataReader["GRADINGBASIS"].ToString(),
dataReader["TYPOFFERED"].ToString(),
dataReader["REMARKS"].ToString(),
dataReader["CAREERID"].ToString());
courses.Add(course);
Logger.Info("ID: " + course.id + " NAME: " + course.name + " DESC: " + course.description);
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::readCourses() " + e.Message);
}
}
public static List<Course> readCareerFinalCourses(Career career)
{
List<Course> courses = new List<Course>();
String readJobsTable = "SELECT COURSEID FROM FinalCourse WHERE CAREERID ='" + career.id + "'";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readJobsTable, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String finalCourseId = dataReader["COURSEID"].ToString();
String readFinalCourse = "SELECT * FROM Course WHERE ID = '" + finalCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readFinalCourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
Course finalCourse = null;
while (dataReaderTemp.Read())
{
finalCourse = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
}
if (finalCourse != null)
{
Logger.Info("[DatabaseConnection::readCareerFinalCourses()] PreRequisite Course Information:");
Logger.Info("[DatabaseConnection::readCareerFinalCourses()]" + "\nID: " + finalCourse.id + "\nNAME: " + finalCourse.name + "\nDESC: " + finalCourse.description);
courses.Add(finalCourse);
}
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::readCareerFinalCourses() " + e.Message);
}
return courses;
}
public static bool isCourseElective(Course targetCourse)
{
bool isElective = false;
String queryStatement = "SELECT * FROM ElectiveCourse WHERE COURSEID = '" + targetCourse.id + "'";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(queryStatement, dbConnection);
dataReader = sqlCommand.ExecuteReader();
isElective = dataReader.Read() ? true : false;
dbConnection.Close();
}
catch (Exception ex)
{
Logger.Error("DatabaseConnection::isCourseElective() " + ex.Message);
}
return isElective;
}
public static List<Course> getRestrictionCourses(String courseId)
{
List<Course> restrictionList = new List<Course>();
String readPreReqs = "SELECT RESTRCOURSEID FROM Restrictions WHERE COURSEID = '" + courseId + "'";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readPreReqs, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String restrCourseId = dataReader["RESTRCOURSEID"].ToString();
String readCourse = "SELECT * FROM Course WHERE ID = '" + restrCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readCourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
while (dataReaderTemp.Read())
{
Course restrCourse = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
Logger.Info("[DatabaseConnection::getRestrictionCourses()] Restricted Course Information:");
Logger.Info("[DatabaseConnection::getRestrictionCourses()]" + "\nID: " + restrCourse.id + "\nNAME: " + restrCourse.name + "\nDESC: " + restrCourse.description);
restrictionList.Add(restrCourse);
}
}
dbConnection.Close();
}
catch (Exception ex)
{
Logger.Error("DatabaseConnection::getRestrictionCourses() " + ex.Message);
}
return restrictionList;
}
public static List<Course> getPrerequisiteCourses(String courseId)
{
List<Course> preRequisiteCourses = new List<Course>();
String readPreReqs = "SELECT * FROM Course WHERE ID = '" + courseId + "'";
Course targetCourse = null;
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readPreReqs, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
targetCourse = new Course(dataReader["ID"].ToString(),
(int)dataReader["YR"],
(int)dataReader["SEM"],
dataReader["NAME"].ToString(),
dataReader["DESC"].ToString(),
(int)dataReader["POINTS"],
dataReader["ACADEMICORG"].ToString(),
dataReader["ACADEMICGROUP"].ToString(),
dataReader["COURSECOMP"].ToString(),
dataReader["GRADINGBASIS"].ToString(),
dataReader["TYPOFFERED"].ToString(),
dataReader["REMARKS"].ToString(),
dataReader["CAREERID"].ToString());
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()] Target Course Information:");
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()]" + "\nID: " + targetCourse.id + "\nNAME: " + targetCourse.name + "\nDESC: " + targetCourse.description);
}
if (targetCourse != null)
{
String readPreReqInfo = "SELECT * FROM PreRequisite WHERE FOLLOWID = '" + targetCourse.id + "'";
sqlCommand = new SQLiteCommand(readPreReqInfo, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
String preReqCourseId = dataReader["COURSEID"].ToString();
String readPreReqCourse = "SELECT * FROM Course WHERE ID = '" + preReqCourseId + "'";
SQLiteCommand sqlCommandTemp = new SQLiteCommand(readPreReqCourse, dbConnection);
SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
while (dataReaderTemp.Read())
{
Course preReq = new Course(dataReaderTemp["ID"].ToString(),
(int)dataReaderTemp["YR"],
(int)dataReaderTemp["SEM"],
dataReaderTemp["NAME"].ToString(),
dataReaderTemp["DESC"].ToString(),
(int)dataReaderTemp["POINTS"],
dataReaderTemp["ACADEMICORG"].ToString(),
dataReaderTemp["ACADEMICGROUP"].ToString(),
dataReaderTemp["COURSECOMP"].ToString(),
dataReaderTemp["GRADINGBASIS"].ToString(),
dataReaderTemp["TYPOFFERED"].ToString(),
dataReaderTemp["REMARKS"].ToString(),
dataReaderTemp["CAREERID"].ToString());
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()] PreRequisite Course Information:");
Logger.Info("[DatabaseConnection::getPrerequisiteCourse()]" + "\nID: " + preReq.id + "\nNAME: " + preReq.name + "\nDESC: " + preReq.description);
preRequisiteCourses.Add(preReq);
}
}
}
dbConnection.Close();
}
catch (Exception ex)
{
Logger.Error("DatabaseConnection::getPrerequisiteCourse() " + ex.Message);
}
return preRequisiteCourses;
}
public static Course getCourse(String courseId)
{
Course course = null;
String readCourseTable = "SELECT * FROM Course WHERE ID = '" + courseId + "'";
try
{
dbConnection.Open();
sqlCommand = new SQLiteCommand(readCourseTable, dbConnection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
course = new Course(dataReader["ID"].ToString(),
(int)dataReader["YR"],
(int)dataReader["SEM"],
dataReader["NAME"].ToString(),
dataReader["DESC"].ToString(),
(int)dataReader["POINTS"],
dataReader["ACADEMICORG"].ToString(),
dataReader["ACADEMICGROUP"].ToString(),
dataReader["COURSECOMP"].ToString(),
dataReader["GRADINGBASIS"].ToString(),
dataReader["TYPOFFERED"].ToString(),
dataReader["REMARKS"].ToString(),
dataReader["CAREERID"].ToString());
Logger.Info("[DatabaseConnection::getCourse()]" + "\nID: " + course.id + "\nNAME: " + course.name + "\nDESC: " + course.description);
}
dbConnection.Close();
}
catch (Exception e)
{
Logger.Error("DatabaseConnection::getCourse() " + e.Message);
}
return course;
}