public void filterCourse()
{
string detail = (comboBoxDetail.SelectedIndex >= 0) ? comboBoxDetail.SelectedValue.ToString() : "base";
string name = txtFilterByName.Text;
int department = (comboBoxDepartment.SelectedIndex >= 0) ? int.Parse(comboBoxDepartment.SelectedValue.ToString()) : 0;
int semester = (comboBoxSemesters.SelectedIndex >= 0) ? Convert.ToInt32(comboBoxSemesters.SelectedValue) : 0;
string degree = (comboBoxFilterByDegree.SelectedIndex >= 0) ? comboBoxFilterByDegree.SelectedValue.ToString() : "";
string query = " WITH res as (SELECT c.id AS id, c.name AS name, c.code AS code, c.ects AS ects, s.name AS semester, c.groups AS groups,c.type AS type, " +
" c.number_of_lectures AS lectures, c.min_days AS min_days, c.degree AS degree, c.double_lectures AS double_lectures," +
" c.number_of_students AS students, cc.name AS parent " +
" FROM courses c " +
" LEFT JOIN courses cc ON cc.id = c.parent_id " +
" LEFT JOIN course_department_rel cdr ON cdr.course_id = c.id " +
" LEFT JOIN departments d ON cdr.department_id = d.id " +
" JOIN semesters s ON c.semester_id = s.id " +
" WHERE c.detail= '" + detail + "'";
if (name != "")
{
query += " AND c.name LIKE '%" + name + "%' ";
}
if (department > 0)
{
query += " AND d.id=" + department;
}
if (semester > 0)
{
query += " AND c.semester_id = '" + semester + "' ";
}
if (degree != "")
{
query += " AND c.degree LIKE '%" + degree + "%' ";
}
query += ") " +
" SELECT MAX(res.id) as id, MAX(res.name) as name, MAX(res.code) as code, MAX(res.code) as code, " +
" MAX(res.ects) as ects, MAX(res.semester) as semester, MAX(res.groups) as groups, MAX(res.type) as type, " +
" MAX(res.lectures) as lectures, MAX(res.min_days) as min_days, MAX(res.degree) as degree, " +
" MAX(cast(res.double_lectures as int)) as double_lectures, " +
" MAX(res.students) as students, MAX(res.parent) as parent, depts = " +
" STUFF((SELECT DISTINCT ', ' + CAST(d.name AS VARCHAR(MAX)) " +
" FROM departments d " +
" JOIN course_department_rel cdr ON cdr.department_id = d.id " +
" WHERE cdr.course_id = MAX(res.id) FOR XMl PATH('')), 1, 1, '' ) " +
" FROM res " +
" GROUP BY res.id " +
" ORDER BY MAX(res.name); ";
coursesGrid.ItemsSource = DBConnection.RAW_Select(query).DefaultView;
}
private void fillRooms()
{
comboBoxRooms.SelectedValuePath = "id";
comboBoxRooms.DisplayMemberPath = "name";
string query = " SELECT id, name, code FROM rooms WHERE id NOT IN ( SELECT room_id FROM courses_rooms_rel WHERE course_id = " + Int32.Parse(txtClassId.Text) + " )";
DataTable res = DBConnection.RAW_Select(query);
ArrayList result = new ArrayList();
foreach (DataRow r in res.Rows)
{
comboBoxRooms.Items.Add(new { id = r.ItemArray[0], name = r.ItemArray[1] });
}
}
private void updateCourseNames()
{
DataTable data = DBConnection.RAW_Select("Select c.name, p.groups, c.lecture_group, c.numeric_group, c.laboratory_group, c.id from courses c" +
" JOIN courses p on c.base_parent_code = p.code Where c.base_parent_code IS not null");
if (data.Rows.Count != 0)
{
foreach (DataRow row in data.Rows)
{
string name = row.ItemArray[0].ToString().Split(new string[] { "_Gr" }, StringSplitOptions.None)[0];
string grField = "";
if (Convert.ToInt32(row.ItemArray[1].ToString()) > 1)
{
grField += " Gr. ";
}
else
{
grField += " ";
}
string lab = row.ItemArray[4].ToString();
string num = row.ItemArray[3].ToString();
string lect = row.ItemArray[2].ToString();
if (lab != null && lab != "")
{
name += grField + row.ItemArray[4].ToString();
}
else if (num != null && num != "")
{
name += grField + row.ItemArray[3].ToString();
}
else if (lect != null && lect != "" && grField != " ")
{
string group = grField + row.ItemArray[2].ToString().Split('r')[1];
name += group;
}
DBConnection.Update(" courses ", " name='" + name + "' ", Convert.ToInt32(row.ItemArray[5].ToString()));
}
}
}
private void OnDoubleClick(object sender, MouseButtonEventArgs e)
{
if (e.LeftButton == MouseButtonState.Pressed && e.ClickCount == 2)
{
foreach (UControl child in childs)
{
if (child.Clicked == "true")
{
if (comboBoxRooms.SelectedIndex != -1)
{
if (child.ControlAssigned == "false" && child.BlockedAssigned != "true")
{
//check if clicked period is a red position
string cCode = txtCode.Text;
int day = int.Parse(child.Column) - 1;
int startPeriod = int.Parse(child.Row) - 1;
string rCode = "";
string query = " SELECT code FROM rooms WHERE id=" + comboBoxRooms.SelectedValue;
DataTable res = DBConnection.RAW_Select(query);
ArrayList result = new ArrayList();
foreach (DataRow r in res.Rows)
{
rCode = r.ItemArray[0].ToString();
}
string values = "'" + cCode + "', '" + rCode + "', '" + day + "', '" + startPeriod + "'";
string table = "timetable_" + getCurrentSemester();
DataView data = DBConnection.Select(table, "'*'", " course_code = '" + cCode + "'").DefaultView;
if (data.Count == 0)
{
int createdId = DBConnection.Create(table, "course_code, room_code, day, start_period", values);
}
else
{
string vals = "room_code='" + rCode + "', day='" + day + "', start_period='" + startPeriod + "'";
int createdId = DBConnection.Update(table, vals, 0, " course_code='" + cCode + "'");
}
MessageBox.Show("Class was placed successfully!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
Close();
break;
}
else
{
MessageBox.Show("Class can not be placed at this position!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
child.Clicked = "false";
break;
}
}
else
{
MessageBox.Show("Please select a room first!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
child.Clicked = "false";
break;
}
}
}
}
}