private void txtFilterByName_TextChanged(object sender, EventArgs e)
{
string name = txtFilterByName.Text;
string teacher = txtFilterByTeacher.Text;
string Columns = " c.id AS course_id, c.name AS course_name," +
" CASE WHEN c.detail = 'lecture' THEN 'Lecture' " +
" WHEN c.detail = 'numeric' THEN 'Numeric Exercise' " +
" WHEN c.detail = 'laboratory' THEN 'Laboratory Exercise'" +
" ELSE '' END AS course_type, " +
" t.id AS teacher_id, concat(t.name, ' ', t.surname) AS teacher_name";
string From = "courses c" +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" LEFT JOIN teachers t ON ctr.teacher_id=t.id ";
string Conditions = " c.parent_id IS NOT NULL ";
if (name != "")
{
Conditions += " AND c.name LIKE '%" + name + "%' ";
}
if (teacher != "")
{
Conditions += " AND concat(t.name, ' ', t.surname) LIKE '%" + teacher + "%'";
}
if (checkBox.IsChecked == true)
{
Conditions += " AND ctr.teacher_id IS NULL ";
}
courseteacherGrid.ItemsSource = DBConnection.Select(From, Columns, Conditions, null, " c.name ").DefaultView;
}
private void GenerateRooms(XmlWriter writer)
{
DataTable data = DBConnection.Select("rooms", " id ");
ArrayList roomIds = new ArrayList();
foreach (DataRow row in data.Rows)
{
roomIds.Add(row.ItemArray[0]);
}
Dictionary <string, string> first = new Dictionary <string, string>();
string roomCode, buildingId, size;
foreach (int rId in roomIds)
{
ArrayList rec = DBConnection.Get("rooms", rId);
first = (Dictionary <string, string>)rec[0];
first.TryGetValue("code", out roomCode);
first.TryGetValue("building_id", out buildingId);
first.TryGetValue("size", out size);
//for every room in rooms table
writer.WriteStartElement("room");
writer.WriteAttributeString("id", roomCode);
writer.WriteAttributeString("size", size);
writer.WriteAttributeString("building", (Convert.ToInt32(buildingId) - 1).ToString());
writer.WriteEndElement();
}
}
private bool CoursePeriodPreferences(UControl child, ref string msg)
{
int cRow = int.Parse(child.Row);
int cColumn = int.Parse(child.Column);
int parentPeriod = ((cRow - 1) / 4) + 1;
bool PeriodContraintViolated = true;
DataTable courseData = DBConnection.Select("course_periods_preferences ", " day, period ", " course_id=" + int.Parse(txtClassId.Text) + " AND day=" + cColumn + " AND period=" + parentPeriod);
if (courseData != null && courseData.Rows.Count > 0)
{
PeriodContraintViolated = true;
}
else
{
PeriodContraintViolated = false;
}
if (PeriodContraintViolated)
{
msg += ((msg == "") ? "" : ", ") + " Teacher doesn't teach at this time";
}
return(PeriodContraintViolated);
}
private void rooms_Initialized(object sender, EventArgs e)
{
string Columns = " r.id AS id, r.name AS name, r.size AS size, r.code AS code, b.name AS building, r.note AS note ";
string Table = " rooms r JOIN buildings b ON r.building_id = b.id";
roomsGrid.ItemsSource = DBConnection.Select(Table, Columns, null, null, " r.name ").DefaultView;
}
private void filter()
{
string name = txtFilterByName.Text;
int size = (txtFilterBySize.Text != "") ? Convert.ToInt32(txtFilterBySize.Text) : 0;
int building = (comboBoxBuildings.SelectedIndex >= 0) ? Convert.ToInt32(comboBoxBuildings.SelectedValue.ToString()) : 0;
string Columns = " r.id AS id, r.name AS name, r.size AS size, r.code AS code, b.name AS building, r.note AS note ";
string Table = " rooms r JOIN buildings b ON r.building_id = b.id";
string Conditions = null;
if (name != "")
{
Conditions = " r.name LIKE '%" + name + "%' ";
}
if (size > 0)
{
Conditions += ((Conditions != null) ? " AND " : " ") + "r.size >= '" + size + "' ";
}
if (building > 0)
{
Conditions += ((Conditions != null) ? " AND " : " ") + "r.building_id = '" + building + "' ";
}
roomsGrid.ItemsSource = DBConnection.Select(Table, Columns, Conditions).DefaultView;
}
private void txtFilterByName_TextChanged(object sender, EventArgs e)
{
string name = txtFilterByName.Text;
string department = txtFilterByDepartment.Text;
string Columns = " c.id AS course_id, c.name AS course_name, " +
" CASE WHEN c.detail = 'lecture' THEN 'Lecture' " +
" WHEN c.detail = 'numeric' THEN 'Numeric Exercise' " +
" WHEN c.detail = 'laboratory' THEN 'Laboratory Exercise'" +
" ELSE '' END AS course_type, " +
"d.id AS department_id, d.name AS department_name";
string From = "courses c" +
" LEFT JOIN course_department_rel cdr ON c.id=cdr.course_id " +
" LEFT JOIN departments d ON cdr.department_id=d.id ";
string Conditions = " c.parent_id IS NOT NULL ";
if (name != "")
{
Conditions += " c.name LIKE '%" + name + "%' ";
}
if (department != "")
{
Conditions += " AND d.name LIKE '%" + department + "%'";
}
if (checkBox.IsChecked == true)
{
Conditions += " AND cdr.department_id IS NULL ";
}
courseDepartmentRelGrid.ItemsSource = DBConnection.Select(From, Columns, Conditions, null, " c.name ").DefaultView;
}
private void filterPeriodPreferences()
{
string name = txtFilterByName.Text;
string teacher = txtFilterByTeacher.Text;
int day = (comboBoxDays.SelectedIndex >= 0) ? Convert.ToInt32(comboBoxDays.SelectedValue.ToString()) : 0;
string Columns = " cp.id AS id, c.id AS course_id, c.name AS course_name, t.id as teacher_id, concat(t.name, ' ', t.surname) AS teacher, d.name AS day, p.name AS period";
string From = "course_periods_preferences cp" +
" JOIN courses c ON cp.course_id=c.id " +
" JOIN days d ON cp.day=d.id " +
" JOIN periods p ON cp.period=p.id " +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" LEFT JOIN teachers t ON ctr.teacher_id=t.id ";
string Conditions = null;
if (name != "")
{
Conditions = " c.name LIKE '%" + name + "%' ";
}
if (teacher != "")
{
Conditions += ((Conditions != null) ? " AND " : "") + " concat(t.name, ' ', t.surname) LIKE '%" + teacher + "%'";
}
if (day > 0)
{
Conditions += ((Conditions != null) ? " AND " : "") + " cp.day= '" + day + "'";
}
courseperiodGrid.ItemsSource = DBConnection.Select(From, Columns, Conditions).DefaultView;
}
private void departments_Initialized(object sender, EventArgs e)
{
string Columns = " d.id AS id, d.name AS name, d.faculty_id AS faculty_id, f.name AS faculty ";
string Table = " departments d JOIN faculties f ON d.faculty_id = f.id";
departmentsGrid.ItemsSource = DBConnection.Select(Table, Columns, null, null, " d.name ").DefaultView;
}
private void saveTeacher_MouseUp(object sender, EventArgs e)
{
if (txtName.Text.Length == 0)
{
MessageBox.Show("Teacher name is required!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if (txtSurname.Text.Length == 0)
{
MessageBox.Show("Teacher surname is required!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if(txtEmail.Text.Length > 0 && !Regex.IsMatch(txtEmail.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"))
{
MessageBox.Show("Please enter a valid email!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if (txtId.Text.Length == 0)
{
string code = null;
for (int i = 1; i < 1000; i++)
{
code = "t" + i.ToString("000");
DataView data = DBConnection.Select("teachers", "'*'", " code = '" + code + "'").DefaultView;
if (data.Count == 0) break;
}
string values = "'" + txtName.Text +
"', '" + txtSurname.Text +
"', '" + txtTitle.Text +
"', '" + txtEmail.Text +
"', '" + txtPhone.Text +
"', '" + code.ToString() +
"', '" + txtNote.Text + "'";
int result = DBConnection.Create("teachers", "name, surname, title, email, phone, code, note", values);
if (result > 0)
{
MessageBox.Show("Successfully created new Teacher!", "Information", MessageBoxButton.OK);
txtId.Text = result.ToString();
}
}
else if (Convert.ToInt32(txtId.Text) > 0)
{
string values = "name='" + txtName.Text +
"', surname='" + txtSurname.Text +
"', title='" + txtTitle.Text +
"', email='" + txtEmail.Text +
"', phone='" + txtPhone.Text +
"', code='" + txtCode.Text +
"', note='" + txtNote.Text + "'";
int result = DBConnection.Update("teachers", values, Convert.ToInt32(txtId.Text));
if (result > 0)
MessageBox.Show("Successfully updated Teacher!", "Information", MessageBoxButton.OK);
}
}
public DataTable getRooms(ArrayList courseIds)
{
string Columns = " DISTINCT(r.id) AS RoomId, r.name AS RoomName";
string From = "rooms r";
string Conditions = " r.id NOT IN (SELECT room_id FROM courses_rooms_rel WHERE course_id IN (" + String.Join(",", courseIds.ToArray()) + ")) ";
DataTable res = DBConnection.Select(From, Columns, Conditions);
return(res);
}
public static ArrayList getSemesters(string conditions = null)
{
DataTable data = DBConnection.Select(" semesters ", " id ", conditions);
ArrayList semesterIds = new ArrayList();
foreach (DataRow semesterRow in data.Rows)
{
semesterIds.Add(semesterRow.ItemArray[0]);
}
return(semesterIds);
}
public ArrayList getTeachers(ArrayList courseIds)
{
string Columns = " DISTINCT(t.id) AS teacherId, concat(t.name, ' ', t.surname) AS teacherName";
string From = "teachers t" +
" LEFT JOIN course_teacher_rel ct ON t.id=ct.teacher_id ";
string Conditions = " ct.course_id IN (" + String.Join(",", courseIds.ToArray()) + ")";
DataTable res = DBConnection.Select(From, Columns, Conditions);
ArrayList result = new ArrayList();
foreach (DataRow r in res.Rows)
{
int lectures = 0, numericEx = 0, laboratoryEx = 0;
string ColumnsQ = " c.detail";
string FromQ = "courses c" +
" LEFT JOIN course_teacher_rel ct ON c.id=ct.course_id ";
string ConditionsQ = " ct.teacher_id ='" + r.ItemArray[0] + "' AND ct.course_id IN (" + String.Join(",", courseIds.ToArray()) + ")";
DataTable teacherCourses = DBConnection.Select(FromQ, ColumnsQ, ConditionsQ);
if (teacherCourses.Rows != null && teacherCourses.Rows.Count > 0)
{
foreach (DataRow course in teacherCourses.Rows)
{
switch (course.ItemArray[0].ToString().Trim())
{
case "lecture":
lectures++;
break;
case "numeric":
numericEx++;
break;
case "laboratory":
laboratoryEx++;
break;
}
}
result.Add(new CourseTeachers()
{
TeacherID = Int32.Parse(r.ItemArray[0].ToString()),
TeacherName = r.ItemArray[1].ToString(),
Lectures = lectures,
NumericExercises = numericEx,
LaboratoryExercises = laboratoryEx
});
}
}
return(result);
}
private void coursePeriodPreferences_Initialized(object sender, EventArgs e)
{
string Columns = " cp.id AS id, c.id AS course_id, c.name AS course_name, t.id as teacher_id, concat(t.name, ' ', t.surname) AS teacher, d.name AS day, p.name AS period";
string From = "course_periods_preferences cp" +
" JOIN courses c ON cp.course_id=c.id " +
" JOIN days d ON cp.day=d.id " +
" JOIN periods p ON cp.period=p.id " +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" LEFT JOIN teachers t ON ctr.teacher_id=t.id ";
courseperiodGrid.ItemsSource = DBConnection.Select(From, Columns).DefaultView;
}
public void initInfo(int tId)
{
comboBoxTeacher.SelectedValue = tId;
DataTable courseData = DBConnection.Select("course_teacher_rel ", " course_id", " teacher_id=" + tId);
if (courseData != null && courseData.Rows.Count > 0)
{
teacherCourses.Clear();
foreach (DataRow course in courseData.Rows)
{
teacherCourses.Add(int.Parse(course.ItemArray[0].ToString()));
}
}
}
private void courseDepartmentRel_Initialized(object sender, EventArgs e)
{
string Columns = " c.id AS course_id, c.name AS course_name," +
" CASE WHEN c.detail = 'lecture' THEN 'Lecture' " +
" WHEN c.detail = 'numeric' THEN 'Numeric Exercise' " +
" WHEN c.detail = 'laboratory' THEN 'Laboratory Exercise'" +
" ELSE '' END AS course_type, " +
" d.id AS department_id, d.name AS department_name";
string From = "courses c" +
" LEFT JOIN course_department_rel cdr ON c.id=cdr.course_id " +
" LEFT JOIN departments d ON cdr.department_id=d.id ";
courseDepartmentRelGrid.ItemsSource = DBConnection.Select(From, Columns, " c.parent_id IS NOT NULL ", null, " c.name ").DefaultView;
}
private void courseTeacherRel_Initialized(object sender, EventArgs e)
{
string Columns = " c.id AS course_id, c.name AS course_name," +
" CASE WHEN c.detail = 'lecture' THEN 'Lecture' " +
" WHEN c.detail = 'numeric' THEN 'Numeric Exercise' " +
" WHEN c.detail = 'laboratory' THEN 'Laboratory Exercise'" +
" ELSE '' END AS course_type, " +
"t.id AS teacher_id, concat(t.name, ' ', t.surname) AS teacher_name";
string From = "courses c" +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" JOIN teachers t ON ctr.teacher_id=t.id ";
courseteacherGrid.ItemsSource = DBConnection.Select(From, Columns, null, null, " c.name ").DefaultView;
}
private void filter()
{
string name = txtFilterByName.Text;
string Columns = " d.id AS id, d.name AS name, d.faculty_id AS faculty_id, f.name AS faculty ";
string Table = " departments d JOIN faculties f ON d.faculty_id = f.id";
string Conditions = null;
if (name != "")
{
Conditions = " d.name LIKE '%" + name + "%' ";
}
departmentsGrid.ItemsSource = DBConnection.Select(Table, Columns, Conditions).DefaultView;
}
private void insertCoursePreferences(int courseId, int teacherId = 0)
{
if (teacherId > 0)
{
DataTable preferences = DBConnection.Select("teacher_periods_preferences", "day, period", "teacher_id=" + teacherId);
if (preferences.Rows.Count != 0)
{
foreach (DataRow preference in preferences.Rows)
{
string values = "'" + courseId + "', '" + preference.ItemArray[0] + "', '" + preference.ItemArray[1] + "'";
int result = DBConnection.Create("course_periods_preferences", "course_id, day, period", values);
}
}
}
}
private void GeneratePeriodConstraints(XmlWriter writer)
{
DataTable courseData = DBConnection.Select("course_periods_preferences cp JOIN courses c ON cp.course_id = c.id ", " Distinct cp.course_id ", " (c.semester_id % 2) =" + semesterModule + " AND c.parent_id IS NOT NULL ");
ArrayList courseIds = new ArrayList();
foreach (DataRow row in courseData.Rows)
{
courseIds.Add(row.ItemArray[0]);
}
foreach (int cId in courseIds)
{
Dictionary <string, string> course = (Dictionary <string, string>)DBConnection.Get("courses", cId)[0];
string courseCode;
course.TryGetValue("code", out courseCode);
writer.WriteStartElement("constraint");
writer.WriteAttributeString("type", "period");
writer.WriteAttributeString("course", courseCode);
DataTable data = DBConnection.Select("course_periods_preferences", " id ", " course_id= " + cId);
ArrayList recIds = new ArrayList();
foreach (DataRow row in data.Rows)
{
recIds.Add(row.ItemArray[0]);
}
Dictionary <string, string> first = new Dictionary <string, string>();
string timeslotDay, timeslotPeriod;
foreach (int pId in recIds)
{
ArrayList preferenceRec = DBConnection.Get("course_periods_preferences", pId);
first = (Dictionary <string, string>)preferenceRec[0];
first.TryGetValue("day", out timeslotDay);
first.TryGetValue("period", out timeslotPeriod);
//for every preference in rooms table
writer.WriteStartElement("timeslot");
writer.WriteAttributeString("day", (Convert.ToInt32(timeslotDay) - 1).ToString());
writer.WriteAttributeString("period", (Convert.ToInt32(timeslotPeriod) - 1).ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();//period constraint
}
}
private void CheckBox_Checked(object sender, RoutedEventArgs e)
{
string Columns = " c.id AS course_id, c.name AS course_name, " +
" CASE WHEN c.detail = 'lecture' THEN 'Lecture' " +
" WHEN c.detail = 'numeric' THEN 'Numeric Exercise' " +
" WHEN c.detail = 'laboratory' THEN 'Laboratory Exercise'" +
" ELSE '' END AS course_type, " +
" CASE WHEN t.id IS NULL THEN '-' ELSE t.id END AS teacher_id, " +
" CASE WHEN t.name IS NULL THEN '-' ELSE concat(t.name, ' ', t.surname) END AS teacher_name";
string From = "courses c" +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" LEFT JOIN teachers t ON ctr.teacher_id=t.id ";
string Conditions = " c.parent_id IS NOT NULL AND ctr.teacher_id IS NULL ";
courseteacherGrid.ItemsSource = DBConnection.Select(From, Columns, Conditions, null, " c.name ").DefaultView;
}
private void GenerateRoomConstraints(XmlWriter writer)
{
DataTable courseData = DBConnection.Select("courses_rooms_rel crr JOIN courses c ON crr.course_id = c.id ", " Distinct crr.course_id ", " (c.semester_id % 2) =" + semesterModule + " AND c.parent_id IS NOT NULL");
ArrayList courseIds = new ArrayList();
foreach (DataRow row in courseData.Rows)
{
courseIds.Add(row.ItemArray[0]);
}
foreach (int cId in courseIds)
{
Dictionary <string, string> course = (Dictionary <string, string>)DBConnection.Get("courses", cId)[0];
string courseCode;
course.TryGetValue("code", out courseCode);
writer.WriteStartElement("constraint");
writer.WriteAttributeString("type", "room");
writer.WriteAttributeString("course", courseCode);
DataTable data = DBConnection.Select("courses_rooms_rel", " room_id ", " course_id= " + cId);
ArrayList recIds = new ArrayList();
foreach (DataRow row in data.Rows)
{
recIds.Add(row.ItemArray[0]);
}
Dictionary <string, string> first = new Dictionary <string, string>();
string roomRef;
foreach (int rId in recIds)
{
ArrayList roomRec = DBConnection.Get("rooms", rId);
first = (Dictionary <string, string>)roomRec[0];
first.TryGetValue("code", out roomRef);
//for every room in courses_rooms_rel table
writer.WriteStartElement("room");
writer.WriteAttributeString("ref", roomRef);
writer.WriteEndElement();
}
writer.WriteEndElement();
}
}
public static ArrayList getDepartments(int id = 0)
{
string conditions = null;
if (id > 0)
{
conditions = " id=" + id;
}
DataTable data = DBConnection.Select(" departments ", " id ", conditions);
ArrayList departmentIds = new ArrayList();
foreach (DataRow departmentRow in data.Rows)
{
departmentIds.Add(int.Parse(departmentRow.ItemArray[0].ToString()));
}
return(departmentIds);
}
private List <Assignment> getAssignments()
{
List <Assignment> assignments = new List <Assignment>();
string columns = " course_code, room_code, day, start_period";
string from = " timetable_" + getCurrentSemester();
DataTable allAssinedCourses = DBConnection.Select(from, columns);
if (allAssinedCourses.Rows != null && allAssinedCourses.Rows.Count > 0)
{
foreach (DataRow course in allAssinedCourses.Rows)
{
assignments.Add(new Assignment(course.ItemArray[0].ToString(), course.ItemArray[1].ToString(), int.Parse(course.ItemArray[2].ToString()), int.Parse(course.ItemArray[3].ToString())));
}
}
return(assignments);
}
private void validateTimetableOpen(LoadingPanel.LoadingPanel bar)
{
string Columns = " c.id AS course_id";
string From = "courses c" +
" LEFT JOIN course_teacher_rel ctr ON c.id=ctr.course_id ";
string Conditions = " c.parent_id IS NOT NULL AND ctr.teacher_id IS NULL ";
DataTable unasignedCoursesT = DBConnection.Select(From, Columns, Conditions);
if (unasignedCoursesT.Rows.Count != 0)
{
MessageBox.Show("Every course should have a teacher assigned! In order to open Timetable window, you have to assign teachers to these courses first!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
CourseTeacherRelation relationGrid = new CourseTeacherRelation();
relationGrid.Owner = this;
relationGrid.checkBox.IsChecked = true;
bar.Close();
relationGrid.ShowDialog();
}
else
{
string ColumnsD = " c.id AS course_id";
string FromD = "courses c" +
" LEFT JOIN course_department_rel cdr ON c.id=cdr.course_id ";
string ConditionsD = " c.parent_id IS NOT NULL AND cdr.department_id IS NULL ";
DataTable unasignedCoursesD = DBConnection.Select(FromD, ColumnsD, ConditionsD);
if (unasignedCoursesD.Rows.Count != 0)
{
MessageBox.Show("Every course should have at least a study program assigned! In order to open Timetable window, you have to assign study programs to these courses first!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
CourseDepartmentRel relationGrid = new CourseDepartmentRel();
relationGrid.Owner = this;
relationGrid.checkBox.IsChecked = true;
bar.Close();
relationGrid.ShowDialog();
}
else
{
TimetablingResultDisplay timetabling = new TimetablingResultDisplay();
timetabling.Owner = this;
bar.Close();
timetabling.ShowDialog();
}
}
}
private bool CoursePeriodPreferences(PreferencesControl child, int teacherId)
{
int cRow = int.Parse(child.Row);
int cColumn = int.Parse(child.Column);
if (teacherId > 0)
{
DataTable teacherData = DBConnection.Select("teacher_periods_preferences ", " day, period ", " teacher_id =" + teacherId + " AND day=" + cColumn + " AND period=" + cRow);
if (teacherData != null && teacherData.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
return(false);
}
private int getTeacherId()
{
int res = 0;
string Columns = " c.id, t.id, c.name";
string From = "courses c" +
" JOIN course_teacher_rel ctr ON c.id=ctr.course_id " +
" JOIN teachers t ON ctr.teacher_id=t.id ";
string Conditions = " c.code='" + Course_code + "'";
DataTable result = DBConnection.Select(From, Columns, Conditions);
foreach (DataRow r in result.Rows)
{
Course_id = int.Parse(r.ItemArray[0].ToString());
res = int.Parse(r.ItemArray[1].ToString());
Course_name = r.ItemArray[2].ToString();
}
return(res);
}
private void txtFilterByName_TextChanged(object sender, TextChangedEventArgs e)
{
string Columns = " id, name, surname, title, email, phone, code, note ";
string Conditions = null;
string name = txtFilterByName.Text;
string surname = txtFilterBySurname.Text;
if (name != "" && surname != "")
{
Conditions = " name LIKE '%" + name + "%' AND surname LIKE '%" + surname + "%' ";
}
else if (name != "")
{
Conditions = " name LIKE '%" + name + "%'";
}
else if (surname != "")
{
Conditions = " surname LIKE '%" + surname + "%'";
}
teachersGrid.ItemsSource = DBConnection.Select("teachers", Columns, Conditions).DefaultView;
}
public ArrayList getDepartments(ArrayList courseIds)
{
string Columns = " DISTINCT(d.id) AS departmentId, d.name AS departmentName";
string From = " departments d" +
" LEFT JOIN course_department_rel cd ON d.id=cd.department_id ";
string Conditions = " cd.course_id IN (" + String.Join(",", courseIds.ToArray()) + ")";
DataTable res = DBConnection.Select(From, Columns, Conditions);
ArrayList result = new ArrayList();
foreach (DataRow r in res.Rows)
{
result.Add(new ComboDepartments()
{
DepartmentID = int.Parse(r.ItemArray[0].ToString()),
DepartmentName = r.ItemArray[1].ToString()
});
}
return(result);
}
private void saveCourseDepartmentRel_MouseUp(object sender, EventArgs e)
{
try
{
if (int.Parse(comboBoxCourse.SelectedIndex.ToString()) < 0)
{
MessageBox.Show("Please select course!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if (int.Parse(comboBoxDepartment.SelectedIndex.ToString()) < 0)
{
MessageBox.Show("Please select department!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
var course_id = comboBoxCourse.SelectedValue;
var department_id = comboBoxDepartment.SelectedValue;
DataView data = DBConnection.Select("course_department_rel", "'*'", " course_id = '" + course_id + "' AND department_id='" + department_id + "'").DefaultView;
if (data.Count != 0)
{
MessageBox.Show("This course is already assigned to the selected department!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
string values = "'" + course_id + "', '" + department_id + "'";
int result = DBConnection.Create("course_department_rel", "course_id, department_id", values);
if (result > 0)
{
MessageBox.Show("Successfully created new course department assignment!", "Information", MessageBoxButton.OK);
}
Close();
} catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK);
}
}
private bool SlotCurriculaConstraint(UControl child, List <Assignment> assignments, ref string msg)
{
bool CurriculaContraintViolated = true;
string columns = " c.id, c.code ";
string from = "courses c" +
" LEFT JOIN course_department_rel cdr ON c.id = cdr.course_id ";
string conditions = "";
switch (txtDetail.Text)
{
case "lecture":
conditions = "c.parent_id IS NOT null AND c.semester_id=" + int.Parse(txtSemester.Text) + " AND cdr.department_id IN (" + txtDepartmentIds.Text + ") AND c.lecture_group='" + txtLectureGroup.Text + "' ";
break;
case "numeric":
conditions = "c.parent_id IS NOT null AND c.semester_id=" + int.Parse(txtSemester.Text) + " AND cdr.department_id IN (" + txtDepartmentIds.Text + ") AND c.lecture_group='" + txtLectureGroup.Text +
"' AND c.id IN (SELECT c1.id FROM courses c1 WHERE (c1.numeric_group='" + txtNumericGroup.Text +
"' OR c1.numeric_group IS NULL) AND c1.lecture_group = c.lecture_group) ";
break;
case "laboratory":
conditions = "c.parent_id IS NOT null AND c.semester_id=" + int.Parse(txtSemester.Text) + " AND cdr.department_id IN (" + txtDepartmentIds.Text + ") AND c.lecture_group='" + txtLectureGroup.Text +
"' AND c.id IN (SELECT c1.id FROM courses c1 WHERE (c1.laboratory_group='" + txtLaboratoryGroup.Text + "' OR c1.laboratory_group IS NULL) AND(c1.numeric_group='" + txtNumericGroup.Text +
"' OR c1.numeric_group IS NULL) AND c1.lecture_group = c.lecture_group) ";
break;
}
DataTable curricula = DBConnection.Select(from, columns, conditions);
if (curricula != null && curricula.Rows.Count > 0)
{
int row = int.Parse(child.Row) - 1;
int column = int.Parse(child.Column) - 1;
foreach (DataRow c in curricula.Rows)
{
//check if students are busy on that slot
List <Assignment> allBusyPeriods = assignments.FindAll(x => x.Course_code == c.ItemArray[1].ToString()).ToList();
if (allBusyPeriods == null)
{
CurriculaContraintViolated = false;
}
else
{
var checkGivenPeriod = allBusyPeriods.Find(x => x.Day == column && x.Starting_period <= row && x.Ending_period > row);
if (checkGivenPeriod == null)
{
CurriculaContraintViolated = false;
}
else
{
msg += ((msg == "") ? "" : ", ") + " Students are busy\n (Class " + checkGivenPeriod.Course_name.Replace("_", ", ") +
" \nfrom " + checkGivenPeriod.getCourseTextPeriod() + ")";
CurriculaContraintViolated = true;
break;
}
}
}
}
else
{
CurriculaContraintViolated = false;
}
return(CurriculaContraintViolated);
}
