public bool GetCourseInformation(string year_semester_)
{
string query = "SELECT COURSE.course_id, COURSE.course_designation, COURSE.course_title, [first_name] & \" \" & [last_name] AS instructor, DEPARTMENT.dept_name, COURSE.credit_hour, COURSE.description, COURSE.prereq_course, COURSE_DETAILS.year_semester, COURSE_DETAILS.max_capacity, COURSE_DETAILS.students_enrolled, COURSE_DETAILS.location, [schedule_days] & \" \" & Format([start_time],\"Short Time\") & \"-\" & Format([end_time],\"Short Time\") AS schedule" +
" FROM (DEPARTMENT INNER JOIN (COURSE INNER JOIN COURSE_DETAILS ON COURSE.course_id = COURSE_DETAILS.course_id) ON DEPARTMENT.dept_id = COURSE.dept_id) INNER JOIN FACULTY ON (DEPARTMENT.dept_id = FACULTY.dept_id) AND (FACULTY.faculty_id = COURSE.instructor_id)" +
" WHERE(( (COURSE_DETAILS.year_semester)='" + year_semester_ + "' and COURSE.course_designation='" + course_designation + "'))";
DataTable dt = GLOBALS.db_query(query);
if (dt.Rows.Count == 1)
{
course_id = dt.Rows[0]["course_id"].ToString();
title = dt.Rows[0]["course_title"].ToString();
instructor = dt.Rows[0]["instructor"].ToString();
department = dt.Rows[0]["dept_name"].ToString();
credit_hours = Convert.ToInt32(dt.Rows[0]["credit_hour"]);
description = dt.Rows[0]["description"].ToString();
prereq_course = dt.Rows[0]["prereq_course"].ToString();
year_semester = dt.Rows[0]["year_semester"].ToString();
capacity = Convert.ToInt32(dt.Rows[0]["max_capacity"]);
enrolled_student = Convert.ToInt32(dt.Rows[0]["students_enrolled"]);
location = dt.Rows[0]["location"].ToString();
schedule = dt.Rows[0]["schedule"].ToString();
return(true);
}
return(false);
}
public static DataTable getCourseList()
{
string query = "SELECT COURSE.course_id, COURSE.course_designation, COURSE.course_title, [first_name] & \" \" & [last_name] AS instructor, DEPARTMENT.dept_name, COURSE.credit_hour, COURSE.description, COURSE.prereq_course, COURSE_DETAILS.year_semester, COURSE_DETAILS.max_capacity, COURSE_DETAILS.students_enrolled, COURSE_DETAILS.location, [schedule_days] & \" \" & Format([start_time],\"Short Time\") & \"-\" & Format([end_time],\"Short Time\") AS schedule" +
" FROM (DEPARTMENT INNER JOIN (COURSE INNER JOIN COURSE_DETAILS ON COURSE.course_id = COURSE_DETAILS.course_id) ON DEPARTMENT.dept_id = COURSE.dept_id) INNER JOIN FACULTY ON (DEPARTMENT.dept_id = FACULTY.dept_id) AND (FACULTY.faculty_id = COURSE.instructor_id)";
return(GLOBALS.db_query(query));
}
public static bool checkValidCourseDesignation(string course_designation)
{
DataTable course_dt = GLOBALS.db_query("select * from COURSE where course_designation = '" + course_designation + "'");
if (course_dt.Rows.Count == 0)
{
return(false);
}
return(true);
}
public static string getCourseIdFromDesignation(string course_designation)
{
DataTable course_dt = GLOBALS.db_query("select course_id from COURSE where course_designation = '" + course_designation + "'");
if (course_dt.Rows.Count == 1)
{
return(course_dt.Rows[0][0].ToString());
}
return(null);
}
public static string getCourseDesignationFromId(string course_id)
{
DataTable course_dt = GLOBALS.db_query("select course_designation from COURSE where course_id = " + course_id);
if (course_dt.Rows.Count == 1)
{
return(course_dt.Rows[0][0].ToString());
}
return(null);
}
public static DataTable getCoursesFromSemester(string semester_number, string program_id)
{
return(GLOBALS.db_query(
"select course_designation, prereq_course " +
"from COURSE " +
"where course_id = any( " +
"select cs.course_id " +
"from course_semester as cs " +
"where cs.semester_id = all( select semester_id from semester " +
"where semester_number = " + semester_number + " and program = " + program_id + "))"));
}
public static string getMaxId()
{
DataTable course_max_id = GLOBALS.db_query("select MAX(course_id) from COURSE");
if (course_max_id.Rows.Count == 1)
{
return(course_max_id.Rows[0][0].ToString());
}
return(null);
}
public static bool checkCourseTaken(Student student, Course course)
{
DataTable dt = GLOBALS.db_query(
"select * from REGISTRATION " +
"where course_id = " + course.course_id +
" and student_id = " + student.student_id +
" and (passed_course = 1 or passed_course is null)");
if (dt.Rows.Count == 0)
{
return(false);
}
return(true);
}
public static List <Course> getCoursesFromFaculty(string faculty_id)
{
List <Course> course_list = new List <Course>();
DataTable course_table = GLOBALS.db_query("select * from course where instructor_id=" + faculty_id);
foreach (DataRow row in course_table.Rows)
{
Course course = new Course(row["course_designation"].ToString());
course.GetCourseInformation(GLOBALS.current_semester);
course_list.Add(course);
}
return(course_list);
}
static public bool Validate()
{
string query = "select * from login where username='******' and password='******'";
DataTable dt = GLOBALS.db_query(query);
if (dt.Rows.Count == 1)
{
member_id = dt.Rows[0]["user_id"].ToString();
membership_type = dt.Rows[0]["Role"].ToString();
return(true);
}
return(false);
}
public static List <Student> getStudentsFromCourseCurrentSemester(string course_id)
{
List <Student> student_list = new List <Student>();
string query = "select * from registration where course_id=" + course_id + " and year_semester='" + GLOBALS.current_semester + "'";
DataTable registration_table = GLOBALS.db_query(query);
foreach (DataRow row in registration_table.Rows)
{
Student student = new Student(row["student_id"].ToString());
student.getStudentInformation();
student_list.Add(student);
}
return(student_list);
}
public static List <Course> getCoursesFromStudent(string student_id, string year_semester)
{
List <Course> course_list = new List <Course>();
string query = "select * from registration where student_id=" + student_id + " and year_semester='" + year_semester + "'";
DataTable registration_table = GLOBALS.db_query(query);
foreach (DataRow row in registration_table.Rows)
{
DataTable course_table = GLOBALS.db_query("select course_designation from course where course_id=" + row["course_id"].ToString());
Course course = new Course(course_table.Rows[0]["course_designation"].ToString());
if (course.GetCourseInformation(GLOBALS.current_semester))
{
course_list.Add(course);
}
}
return(course_list);
}
public bool getStudentInformation()
{
DataTable student_table = GLOBALS.db_query("select * from student where student_id=" + student_id);
if (student_table.Rows.Count == 1)
{
first_name = student_table.Rows[0]["first_name"].ToString();
last_name = student_table.Rows[0]["last_name"].ToString();
phone_number = student_table.Rows[0]["phone_number"].ToString();
email = student_table.Rows[0]["email"].ToString();
department_id = student_table.Rows[0]["dept_id"].ToString();
program_id = student_table.Rows[0]["program_id"].ToString();
current_semester_id = student_table.Rows[0]["current_semester"].ToString();
date_admission = student_table.Rows[0]["date_admission"].ToString();
current_credit_hour = Convert.ToInt32(student_table.Rows[0]["current_credit_hr"]);
// get information from department
DataTable department_table = GLOBALS.db_query("select * from department where dept_id=" + department_id);
if (department_table.Rows.Count == 1)
{
department = department_table.Rows[0]["dept_name"].ToString();
}
// get information from program
DataTable program_table = GLOBALS.db_query("select * from program where program_id=" + program_id);
if (program_table.Rows.Count == 1)
{
program_title = program_table.Rows[0]["program_title"].ToString();
}
// get information from semester
DataTable semester_table = GLOBALS.db_query("select * from semester where semester_id=" + current_semester_id);
if (semester_table.Rows.Count == 1)
{
current_semester = semester_table.Rows[0]["semester_number"].ToString();
max_credit_hour_allowed = Convert.ToInt32(semester_table.Rows[0]["total_credits"]);
}
return(true);
}
return(false);
}
public bool getFalcutyInformation()
{
DataTable falcuty = GLOBALS.db_query("select * from faculty where faculty_id=" + faculty_id);
if (falcuty.Rows.Count == 1)
{
first_name = falcuty.Rows[0]["first_name"].ToString();
last_name = falcuty.Rows[0]["last_name"].ToString();
phone_number = falcuty.Rows[0]["phone_number"].ToString();
email = falcuty.Rows[0]["email"].ToString();
department_id = falcuty.Rows[0]["dept_id"].ToString();
DataTable department_table = GLOBALS.db_query("select * from department where dept_id=" + department_id);
if (department_table.Rows.Count == 1)
{
department = department_table.Rows[0]["dept_name"].ToString();
}
return(true);
}
return(false);
}
public static bool checkCoursePrereqNotTaken(Student student, Course course)
{
// if there is no pre-req course
if (course.prereq_course == null || course.prereq_course == "")
{
return(false);
}
DataTable dt = GLOBALS.db_query(
"select * from REGISTRATION " +
"where course_id = " + course.prereq_course +
" and student_id = " + student.student_id +
" and passed_course = 1");
if (dt.Rows.Count == 1)
{
return(false);
}
return(true);
}
public static DataTable getStudentList()
{
return(GLOBALS.db_query("select student_id, [first_name] & \" \" & [last_name] AS name, email from STUDENT"));
}
public static DataTable getSemesterList()
{
return(GLOBALS.db_query(
"select distinct year_semester from COURSE_DETAILS"
));
}
public static DataTable getInstructorList()
{
return(GLOBALS.db_query(
"select faculty_id, [first_name] & \" \" & [last_name] AS name from Faculty"
));
}
public static DataTable getDepartmentList()
{
return(GLOBALS.db_query(
"select * from Department"
));
}