//If lecturer radio button changed
private void Lecturer_radio_CheckedChanged(object sender, EventArgs e)
{
//If the radio button is checked
if (Lecturer_radio.Checked == true)
{
//Clear chart
chart.Series["Work Hours"].Points.Clear();
//Enable combobox of lecturers
lecturers_combo.Enabled = true;
string[] days = new string[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" };
//Get name and id of the lecturer
DataRowView oDataRowView = lecturers_combo.SelectedItem as DataRowView;
string name = oDataRowView.Row["Name"] as string;
int id = (int)oDataRowView.Row["ID"];
//Change title
chart_title.Text = name + " work hours";
lecturers_combo.Enabled = true;
int workHours;
DataSet LecturerDS;
//Get all lectures of the lecturer
LecturerDS = SqlWorker.GetDataSet("Select * From Lecture Where Lecturer='" + id + "'");
for (int i = 1; i <= 6; i++)
{
//Add each day work hours to the chart
workHours = SqlWorker.getWorkHours(Convert.ToInt32(id), false, i);
chart.Series["Work Hours"].Points.AddXY(days[i - 1], workHours);
}
//Remove lables with zero values in the pie chart
updatePieChart();
}
else
{
lecturers_combo.Enabled = false;
}
}
public void Test_add_new_course()
{
//Add course with lecture, practice and lab
//Limit lecture, practice and lab duration
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, PracticeDuration, LabDuration, true);
Assert.IsTrue(SqlWorker.GetDataSet("select * from Courses where CourseName='" + courseName +
"' and Lecturer=" + lecID + " and practitioner=" + PracID + " and MaxStudent=20 and " +
"LectureDuration=" + LecDuration + " and PracticeDuration=" + PracDuration + " and LabDuration=" + LabDur +
" and Must=" + must).Tables[0].Rows.Count == 1);
SqlWorker.ExecuteQueries("Delete from Courses where CourseName='" + courseName + "'");
//Add course with lecture and practice without lab
//Limit lecture and practice duration
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, PracticeDuration, null, true);
Assert.IsTrue(SqlWorker.GetDataSet("select * from Courses where CourseName='" + courseName +
"' and Lecturer=" + lecID + " and practitioner=" + PracID + " and MaxStudent=20 and " +
"LectureDuration=" + LecDuration + " and PracticeDuration=" + PracDuration +
" and Must=" + must + " and LabDuration is null").Tables[0].Rows.Count == 1);
SqlWorker.ExecuteQueries("Delete from Courses where CourseName='" + courseName + "'");
//Add course with lecture and lab
//Limit lecture and lab duration
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, null, LabDuration, true);
Assert.IsTrue(SqlWorker.GetDataSet("select * from Courses where CourseName='" + courseName +
"' and Lecturer=" + lecID + " and practitioner=" + PracID + " and MaxStudent=20 and " +
"LectureDuration=" + LecDuration + " and LabDuration=" + LabDur +
" and Must=" + must + " and PracticeDuration is null").Tables[0].Rows.Count == 1);
SqlWorker.ExecuteQueries("Delete from Courses where CourseName='" + courseName + "'");
}
//If all practitioners radio button was changed
private void all_practitioner_radio_CheckedChanged(object sender, EventArgs e)
{
//If the radio button is checked
if (all_practitioner_radio.Checked == true)
{
//Clear chart
chart.Series["Work Hours"].Points.Clear();
//Change title
chart_title.Text = "Practitioners work hours";
int amountOfPractitioners;
//Get table containing all the practitioners
DataSet practitionersDS = SqlWorker.GetDataSet("Select * from Users where type='Practitioner'");
amountOfPractitioners = SqlWorker.getAmountOfPractitioners();
//Get work hours for each practitioner and put it on the chart
for (int i = 0; i < amountOfPractitioners; i++)
{
String name = practitionersDS.Tables[0].Rows[i]["Name"].ToString();
String id = practitionersDS.Tables[0].Rows[i]["ID"].ToString();
int workHours = SqlWorker.getWorkHours(Convert.ToInt32(id), true, -1);
chart.Series["Work Hours"].Points.AddXY(name, workHours);
}
//Remove lables with zero values in the pie chart
updatePieChart();
}
}
public static int setClassRequest(string id, string CourseName, int proj_s, int lab_s)
{
DataSet d = SqlWorker.GetDataSet("SELECT TeacherId FROM ClassRequest WHERE TeacherId = '" + id + "' AND CourseName LIKE '" + CourseName + "'");
string query;
try
{
if (d.Tables[0].Rows.Count == 0)
{
query = "INSERT INTO ClassRequest(TeacherId,CourseName,Projector,Lab) VALUES('" + id + "','" + CourseName + "','" + proj_s + "','" + lab_s + "')";
SqlWorker.ExecuteQueries(query);
return(1);
}
else
{
query = "UPDATE ClassRequest SET Projector = '" + proj_s + "' ,Lab = '" + lab_s + "' WHERE TeacherId = '" + id + "' AND CourseName LIKE '" + CourseName + "'";
SqlWorker.ExecuteQueries(query);
return(2);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Ctor
public Set_pres_courses()
{
InitializeComponent();
comboBox1.DataSource = SqlWorker.GetDataSet("SELECT CourseName From Courses").Tables[0];
comboBox1.DisplayMember = "CourseName";
comboBox2.DataSource = SqlWorker.GetDataSet("SELECT CourseName From Courses").Tables[0];
comboBox2.DisplayMember = "CourseName";
this.Size = new Size(404, 150);
}
public void Test_check_course_existence()
{
//Add new course
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, PracticeDuration, LabDuration, true);
Assert.IsTrue(SqlWorker.GetDataSet("select * from Courses where CourseName='" + courseName +
"' and Lecturer=" + lecID + " and practitioner=" + PracID + " and MaxStudent=20 and " +
"LectureDuration=" + LecDuration + " and PracticeDuration=" + PracDuration + " and LabDuration=" + LabDur +
" and Must=" + must).Tables[0].Rows.Count == 1 && SqlWorker.checkCourseExistence(courseName));
SqlWorker.ExecuteQueries("Delete from Courses where CourseName='" + courseName + "'");
}
public void TestAddLecturer()
{
Adding_User_Form form = new Adding_User_Form();
form.addUser(LecturerID, password, name, type, startTime, endTime, day);
try
{
Assert.IsTrue(SqlWorker.GetDataSet("SELECT * from Users WHERE ID LIKE '" + LecturerID + "' AND PWD LIKE '" + password + "' AND Name LIKE '" + name + "' AND Type LIKE '" + type + "'").Tables[0].Rows.Count == 1);
}
catch
{
Assert.Fail();
}
SqlWorker.GetDataSet("DELETE FROM Users WHERE ID='" + LecturerID + "'");
}
//Show all pre courses
private void view_pre_courses_Click(object sender, EventArgs e)
{
if (view_pre_courses.Text == "View pre courses")
{
view_pre_courses.Text = "Hide pre courses";
dataGridView1.Visible = true;
this.Size = new Size(404, 325);
dataGridView1.DataSource = SqlWorker.GetDataSet("SELECT * From PreCourses").Tables[0];
}
else
{
view_pre_courses.Text = "View pre courses";
dataGridView1.Visible = false;
this.Size = new Size(404, 150);
}
}
//Show course list
private void View_courses_Click(object sender, EventArgs e)
{
if (View_courses.Text == "View courses")
{
View_courses.Text = "Hide courses";
this.Size = new Size(351, 561);
dataGridView1.Visible = true;
dataGridView1.DataSource = SqlWorker.GetDataSet("SELECT CourseId,CourseName From Courses").Tables[0];
}
else
{
View_courses.Text = "View courses";
this.Size = new Size(351, 375);
dataGridView1.Visible = false;
}
}
//Constructor
public Add_new_course()
{
InitializeComponent();
this.Size = new Size(351, 375);
if (SqlWorker.getAmountOfLecturers() > 1 && SqlWorker.getAmountOfPractitioners() > 1)
{
LecturerID_combo.DataSource = SqlWorker.GetDataSet("SELECT * From Users Where type='Lecturer'").Tables[0];
LecturerID_combo.DisplayMember = "Name";
practitionerID_combo.DataSource = SqlWorker.GetDataSet("SELECT * From Users Where type='Practitioner'").Tables[0];
practitionerID_combo.DisplayMember = "Name";
}
else
{
MessageBox.Show("There is no practitioners/lecturers in the system!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
}
public void TestFilterPractice()
{
Searching_Filtering_Practices_Form form = new Searching_Filtering_Practices_Form();
SqlWorker.GetDataSet("INSERT INTO TeachingExercises(StartTime,EndTime,Class,Practitioner,type,CourseName,Day,AmountOfStudents) VALUES('" + startTime + "','" + endTime + "','" + Class + "','" + practitioner + "','" + type + "','" + CourseName + "','" + day + "','" + amoOfStud + "')");
DataSet data = SqlWorker.GetDataSet("SELECT ID FROM TeachingExercises WHERE StartTime LIKE '" + startTime + "' AND EndTime LIKE '"
+ endTime + "' AND Class LIKE '" + Class + "' AND Practitioner LIKE '" + practitioner + "' AND type LIKE '" + type + "' AND CourseName LIKE '" + CourseName + "' AND Day LIKE '"
+ day + "' AND AmountOfStudents LIKE '" + amoOfStud + "'");
try
{
Assert.IsTrue(form.searchPractice(startTime, endTime, Class, practitioner, type, data.Tables[0].Rows[0][0].ToString()).Tables[0].Rows.Count == 1);
}
catch
{
Assert.Fail();
}
SqlWorker.GetDataSet("DELETE FROM TeachingExercises WHERE ID LIKE '" + data.Tables[0].Rows[0][0].ToString() + "'");
}
public void TestFilterLecturer()
{
Searching_Filtering_Lectures_Form form = new Searching_Filtering_Lectures_Form();
SqlWorker.GetDataSet("INSERT INTO Lecture(StartTime,EndTime,Class,Lecturer,CourseName,Day,AmountOfStudents) VALUES('" + startTime + "','" + endTime + "','" + Class + "','" + lecturer + "','" + CourseName + "','" + day + "','" + amoOfStud + "')");
DataSet data = SqlWorker.GetDataSet("SELECT ID FROM Lecture WHERE StartTime LIKE '" + startTime + "' AND EndTime LIKE '"
+ endTime + "' AND Class LIKE '" + Class + "' AND Lecturer LIKE '" + lecturer + "' AND CourseName LIKE '" + CourseName + "' AND Day LIKE '"
+ day + "' AND AmountOfStudents LIKE '" + amoOfStud + "'");
try
{
Assert.IsTrue(form.searchLecture(startTime, endTime, Class, lecturer, data.Tables[0].Rows[0][0].ToString()).Tables[0].Rows.Count == 1);
}
catch
{
Assert.Fail();
}
SqlWorker.GetDataSet("DELETE FROM Lecture WHERE ID LIKE '" + data.Tables[0].Rows[0][0].ToString() + "'");
}
//Ctor
public Work_Hours()
{
InitializeComponent();
//Fill comboboxes of lecturers and practitioners
if (SqlWorker.getAmountOfLecturers() > 0 || SqlWorker.getAmountOfPractitioners() > 0)
{
chart_type_combo.SelectedIndex = 0;
if (SqlWorker.getAmountOfLecturers() > 0)
{
lecturers_combo.DataSource = SqlWorker.GetDataSet("SELECT * From Users Where type='Lecturer'").Tables[0];
lecturers_combo.DisplayMember = "Name";
lecturers_combo.SelectedIndex = 0;
}
else
{
All_lectures_radio.Enabled = false;
Lecturer_radio.Enabled = false;
lecturers_combo.Enabled = false;
}
if (SqlWorker.getAmountOfPractitioners() > 0)
{
practitioner_combo.DataSource = SqlWorker.GetDataSet("SELECT * From Users Where type='Practitioner'").Tables[0];
practitioner_combo.DisplayMember = "Name";
practitioner_combo.SelectedIndex = 0;
}
else
{
all_practitioner_radio.Enabled = false;
practitioner_radio.Enabled = false;
practitioner_combo.Enabled = false;
}
}
else
{
MessageBox.Show("There is no practitioners/lecturers in the system!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
All_lectures_radio.Checked = true;
}
public void Test_Search_Class()
{
Search_Class_By_Type_Form form = new Search_Class_By_Type_Form();
int count = 0;
try
{
count = form.searchClass(true, true).Tables[0].Rows.Count;
}
catch {}
SqlWorker.GetDataSet("INSERT INTO Class(Room,Capacity,IsLab,HasProjector,Hours,NumOfComputers,Days) VALUES('" + room + "','" + capacity + "','" + islab + "','" + hasProjector + "','" + hours + "','" + numComp + "','" + days + "')");
try
{
Assert.IsTrue(form.searchClass(true, true).Tables[0].Rows.Count == count + 1);
}
catch
{
Assert.Fail();
}
SqlWorker.GetDataSet("DELETE FROM Class WHERE Room LIKE '" + room + "'");
}
public void class_req_Test()
{
//insert one request for class in temp course.
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, PracticeDuration, LabDuration, true);
SqlWorker.setClassRequest(lecID.ToString(), courseName, p, l);
Assert.IsTrue(SqlWorker.GetDataSet("select * from ClassRequest where TeacherID LIKE '" + lecID +
"' AND CourseName LIKE '" + courseName + "' AND Projector = '" + p + "' AND Lab = '" + l + "'").Tables[0].Rows.Count == 1);
//try to update the request
p = 0;
SqlWorker.addCourse(courseName, lecturerID, practitionerID, LectureDuration, PracticeDuration, LabDuration, true);
SqlWorker.setClassRequest(lecID.ToString(), courseName, p, l);
Assert.IsTrue(SqlWorker.GetDataSet("select * from ClassRequest where TeacherID LIKE '" + lecID +
"' AND CourseName LIKE '" + courseName + "' AND Projector = '" + p + "' AND Lab = '" + l + "'").Tables[0].Rows.Count == 1);
//delete the temp course.
SqlWorker.ExecuteQueries("Delete from Courses where CourseName='" + courseName + "'");
//delete the request
SqlWorker.ExecuteQueries("Delete from ClassRequest where CourseName='" + courseName + "'");
}
//Add pre course
//0 - already exists
//1 - opposite exists
//2 - operation successfull
public static int addPreCourse(string preCourse, string course)
{
//Check if the pre course is already a pre course of the course
DataSet ds = SqlWorker.GetDataSet("SELECT * from PreCourses where PreCourse='" + preCourse + "' and course='" + course + "'");
if (ds.Tables[0].Rows.Count != 0)
{
return(0);
}
ds = SqlWorker.GetDataSet("SELECT * from PreCourses where PreCourse='" + course + "' and course='" + preCourse + "'");
if (ds.Tables[0].Rows.Count != 0)
{
return(1);
}
SqlConnection sConn = new SqlConnection(Globals.connString);
sConn.Open();
SqlCommand cmd = new SqlCommand("Insert into PreCourses Values('" + preCourse + "','" + course + "')", sConn);
cmd.ExecuteNonQuery();
sConn.Close();
return(2);
}
private void practitioner_most_button_Click(object sender, EventArgs e)
{
//Get all practitioners
DataSet practitioners = SqlWorker.GetDataSet("Select * from Users Where type='Practitioner'");
//Show error message and exit if there is no practitioners
if (practitioners.Tables[0].Rows.Count == 0)
{
MessageBox.Show("There is no practitioners in the system!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int maxWorkHours = 0, id, numOfLecturers = 0;
//Find max of work hours
for (int i = 0; i < practitioners.Tables[0].Rows.Count; i++)
{
id = Convert.ToInt32(practitioners.Tables[0].Rows[i]["ID"].ToString());
int tmpWorkHours = SqlWorker.getWorkHours(id, true, -1);
if (tmpWorkHours > maxWorkHours)
{
maxWorkHours = tmpWorkHours;
}
}
//Show error message and exit if none of the practitioners is working
if (maxWorkHours == 0)
{
MessageBox.Show("There is no practitioner that is working!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Find the amount of practitioners with the maximum amount of work hours
for (int i = 0; i < practitioners.Tables[0].Rows.Count; i++)
{
id = Convert.ToInt32(practitioners.Tables[0].Rows[i]["ID"].ToString());
if (maxWorkHours == SqlWorker.getWorkHours(id, true, -1))
{
numOfLecturers++;
}
}
String msg;
//Create the message
if (numOfLecturers == 1)
{
msg = "The practitioner with the most hours is\n";
}
else
{
msg = "The practitioners with the most hours are\n";
}
//Add all practitioners to the message
for (int i = 0; i < practitioners.Tables[0].Rows.Count; i++)
{
id = Convert.ToInt32(practitioners.Tables[0].Rows[i]["ID"].ToString());
if (maxWorkHours == SqlWorker.getWorkHours(id, true, -1))
{
msg += "Name: " + SqlWorker.getNameById(id.ToString()) + "\n";
msg += "ID: " + id + "\n";
msg += "Number of work hours: " + maxWorkHours.ToString() + "\n\n";
}
}
//Show all lecturers with maximum amount of work hours
MessageBox.Show(msg, "Works the most", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
public static int getAmountOfPractitioners()
{
DataSet ds = SqlWorker.GetDataSet("Select * FROM Users WHERE type='Practitioner'");
return(ds.Tables[0].Rows.Count);
}