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 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 bool addRegistration(Student student, Course course)
{
string query = "insert into REGISTRATION (course_id, student_id, year_semester)" +
"values (" + course.course_id + ", " + student.student_id + ", '" + GLOBALS.current_semester + "')";
return(GLOBALS.db_command(query));
}
private void btnLogin_Click(object sender, EventArgs e)
{
LoginController login_controller = new LoginController();
if (!GLOBALS.isAlphaNumeric(txtUsername.Text) ||
!GLOBALS.isAlphaNumeric(txtPassword.Text))
{
return;
}
if (login_controller.validateLogin(txtUsername.Text, txtPassword.Text))
{
MessageBox.Show("Success!");
this.Hide();
if (Member.membership_type == "administrator" || Member.membership_type == "data entry operator")
{
HomePageAdmin homepage_admin = new HomePageAdmin();
homepage_admin.ShowDialog();
}
else if (Member.membership_type == "student" || Member.membership_type == "faculty")
{
HomePage homepage = new HomePage();
homepage.ShowDialog();
}
txtUsername.Text = "";
txtPassword.Text = "";
this.Show();
}
else
{
MessageBox.Show("Your username and password are not match!");
}
}
public bool updateEnrolledStudent(int new_enrolled_student)
{
enrolled_student = new_enrolled_student;
string query = "update COURSE_DETAILS set students_enrolled = " + enrolled_student +
" where course_id = " + course_id + " and year_semester= '" + GLOBALS.current_semester + "'";
return(GLOBALS.db_command(query));
}
public static bool delete(string course_id)
{
string query = "delete from COURSE_DETAILS where " +
"course_id= " + course_id +
" and year_semester='" + GLOBALS.current_semester + "'";
return(GLOBALS.db_command(query));
}
public bool updateCreditHour(int new_credit_hour)
{
current_credit_hour = new_credit_hour;
string query = "update STUDENT set current_credit_hr = " + current_credit_hour +
" where student_id = " + student_id;
return(GLOBALS.db_command(query));
}
public static bool dropRegistration(Student student, Course course)
{
string query = "delete from REGISTRATION where " +
"course_id= " + course.course_id +
" and student_id =" + student.student_id +
" and year_semester='" + GLOBALS.current_semester + "'";
return(GLOBALS.db_command(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);
}
private void btnReportCourseDetails_Click(object sender, EventArgs e)
{
if (txtCourseID.Text != "" && GLOBALS.isAlphaNumeric(txtCourseID.Text))
{
if (GenerateReportController.reportCourseDetail(txtCourseID.Text))
{
MessageBox.Show("Report generated!");
}
else
{
MessageBox.Show("Fail to generate report! That course is not offered this semester!");
}
}
}
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 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 <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 bool update(
string course_id,
string title,
string course_designation,
string instructor_id,
string department_id,
string num_credit,
string course_description,
string pre_req_course_id,
string max_capacity,
string enrolled_student,
string semester)
{
string query;
if (pre_req_course_id == null)
{
pre_req_course_id = "Null";
}
query = "update COURSE set course_title = '" + title + "', "
+ "course_designation = '" + course_designation + "', "
+ "instructor_id = " + instructor_id + ", "
+ "description = '" + course_description + "', "
+ "prereq_course = " + pre_req_course_id + ", "
+ "dept_id = " + department_id + ", "
+ "credit_hour = " + num_credit + " "
+ " where course_id = " + course_id;
if (!GLOBALS.db_command(query))
{
return(false);
}
query = "update COURSE_DETAILS set "
+ "max_capacity = " + max_capacity + ", "
+ "students_enrolled = " + enrolled_student + " "
+ "where course_id = " + course_id + " "
+ "and year_semester = '" + semester + "'";
if (!GLOBALS.db_command(query))
{
return(false);
}
return(true);
}
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 bool add(
string course_id,
string title,
string course_designation,
string instructor_id,
string department_id,
string num_credit,
string course_description,
string pre_req_course_id,
string max_capacity,
string enrolled_student,
string semester)
{
string query;
if (pre_req_course_id == null)
{
query = "insert into COURSE (course_id, course_title, course_designation, instructor_id, dept_id, credit_hour, description) " +
"values (" + course_id + ", '" + title + "', '" + course_designation + "', " + instructor_id + ", " + department_id + ", " + num_credit + ", '" + course_description + "')";
}
else
{
query = "insert into COURSE (course_id, course_title, course_designation, instructor_id, dept_id, credit_hour, description, prereq_course) " +
"values (" + course_id + ", '" + title + "', '" + course_designation + "', " + instructor_id + ", " + department_id + ", " + num_credit + ", '" + course_description + "', " + pre_req_course_id + ")";
}
if (!GLOBALS.db_command(query))
{
return(false);
}
query = "insert into COURSE_DETAILS (course_id, max_capacity, students_enrolled, year_semester) " +
"values (" + course_id + ", " + max_capacity + ", " + "0" + ", '" + semester + "')";
if (!GLOBALS.db_command(query))
{
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"
));
}