public List<Course> GetStudentCourses(string regNo)
{
try
{
int serialNo = 1;
connection.Open();
string query =
"SELECT c.courseCode,c.courseTitle FROM t_Course c join t_StudentCourseEnroll e on(c.courseId=e.courseId) where e.registationNo=@regNo";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@regNo", regNo);
List<Course> aStudentCourses = new List<Course>();
SqlDataReader studentCourseReader = command.ExecuteReader();
while (studentCourseReader.Read())
{
Course aCourse = new Course();
aCourse.CourseCode = studentCourseReader[0].ToString();
aCourse.CourseName = studentCourseReader[1].ToString();
aCourse.CourseId = serialNo;
serialNo++;
aStudentCourses.Add(aCourse);
}
return aStudentCourses;
}
finally
{
connection.Close();
}
}
public List<Course> GetAllUnscheduledCourses(Course aCourse)
{
List<Course> unscheduleCourseList = new List<Course>();
aCourseGateway = new CourseGateway();
List<Course> courseList = new List<Course>();
courseList = aCourseGateway.GetAllCoursesByDepartment(aCourse.ADepartment.DepartmentId,
aCourse.ASemester.SemesterId);
List<int> coursesId = new List<int>();
int status;
coursesId = aCourseGateway.GetScheduleCoursesId(aCourse);
foreach (Course course in courseList)
{
status = 0;
foreach (int courseId in coursesId)
{
if (courseId == course.CourseId)
{
status = 1;
}
}
if (status == 0)
{
unscheduleCourseList.Add(course);
}
}
return unscheduleCourseList;
}
public List<Course> GetAllUnassignedCourses(Course aCourse)
{
try
{
connection.Open();
List<Course> courses = new List<Course>();
string query = "SELECT courseCode,courseTitle,credit,courseDescription FROM t_Course WHERE CourseStatus=@courseStatus AND DepartmentId=@departmentId AND SemesterId=@semesterId";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@courseStatus", aCourse.CourseStatus);
command.Parameters.AddWithValue("@departmentId", aCourse.ADepartment.DepartmentId);
command.Parameters.AddWithValue("@semesterId", aCourse.ASemester.SemesterId);
SqlDataReader courseReader = command.ExecuteReader();
while (courseReader.Read())
{
Course course = new Course();
course.CourseCode = courseReader[0].ToString();
course.CourseName = courseReader[1].ToString();
course.Credit = Convert.ToDouble(courseReader[2].ToString());
course.Description = courseReader[3].ToString();
courses.Add(course);
}
return courses;
}
finally
{
connection.Close();
}
}
public List<Course> GetAllCourses()
{
try
{
List<Course> courses = new List<Course>();
connection.Open();
string query = "SELECT * FROM T_COURSE";
command.CommandText = query;
SqlDataReader courseReader = command.ExecuteReader();
while (courseReader.Read())
{
Course aCourse = new Course();
aCourse.CourseId = Convert.ToInt16(courseReader[0].ToString());
aCourse.CourseCode = courseReader[1].ToString();
aCourse.CourseName = courseReader[2].ToString();
aCourse.Credit = float.Parse(courseReader[3].ToString());
aCourse.Description = courseReader[4].ToString();
aCourse.ASemester = new Semester();
aCourse.ADepartment = new Department();
aCourse.ASemester.SemesterId = Convert.ToInt16(courseReader[5].ToString());
aCourse.ADepartment.DepartmentId = Convert.ToInt16(courseReader[6].ToString());
aCourse.CourseStatus = Convert.ToInt16(courseReader[7].ToString());
courses.Add(aCourse);
}
return courses;
}
finally
{
connection.Close();
}
}
protected void saveButton_Click(object sender, EventArgs e)
{
if (!IsValid)
{
return;
}
try
{
Course aCourse = new Course();
aCourse.CourseCode = codeTextBox.Value;
aCourse.CourseName = titleTextBox.Value;
aCourse.Credit = Convert.ToDouble(creditTextBox.Value);
aCourse.Description = descriptionTextBox.Value;
aCourse.CourseStatus = 0;
aDepartmentManager = new DepartmentManager();
aCourse.ADepartment = aDepartmentManager.GetDepartment(Convert.ToInt16(departmentDropDownList.Text));
SemesterManager aSemesterManager = new SemesterManager();
aCourse.ASemester = aSemesterManager.GetSemester(Convert.ToInt16(semesterDropDownList.Text));
CourseManager aCourseManager = new CourseManager();
string msg = aCourseManager.SaveCourse(aCourse);
if (msg == "Saved")
{
msgLabel.ForeColor = Color.Green;
msgLabel.Text = msg;
ClearAllTextBoxes();
}
else
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = msg;
}
}
catch (SqlException sqlException)
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = "Database error.See details error: " + sqlException.Message;
}
catch (Exception exception)
{
msgLabel.ForeColor = Color.Red;
string errorMessage = "Unknow error occured.";
errorMessage += exception.Message;
if (exception.InnerException != null)
{
errorMessage += exception.InnerException.Message;
}
msgLabel.Text = errorMessage;
}
}
public Course GetCourse(int courseId)
{
Course aCourse = new Course();
List<Course> courses = new List<Course>();
courses = GetAllCourses();
foreach (Course course in courses)
{
if (course.CourseId == courseId)
{
aCourse = course;
}
}
return aCourse;
}
public List<Course> GetAllCoursesByDepartment(int departmentId, int semesteId)
{
try
{
List<Course> courses = new List<Course>();
connection.Open();
int status = 0;
string query = "SELECT * FROM T_COURSE WHERE departmentId=@deptId AND semesterId=@semeId AND courseStatus=@status";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@deptId", departmentId);
command.Parameters.AddWithValue("@semeId", semesteId);
command.Parameters.AddWithValue("@status", status);
SqlDataReader courseReader = command.ExecuteReader();
while (courseReader.Read())
{
Course aCourse = new Course();
aCourse.CourseId = Convert.ToInt16(courseReader[0].ToString());
aCourse.CourseCode = courseReader[1].ToString();
aCourse.CourseName = courseReader[2].ToString();
aCourse.Credit = float.Parse(courseReader[3].ToString());
aCourse.Description = courseReader[4].ToString();
aCourse.ASemester = new Semester();
aCourse.ADepartment = new Department();
aCourse.ASemester.SemesterId = Convert.ToInt16(courseReader[5].ToString());
aCourse.ADepartment.DepartmentId = Convert.ToInt16(courseReader[6].ToString());
aCourse.CourseStatus = Convert.ToInt16(courseReader[7].ToString());
courses.Add(aCourse);
}
return courses;
}
finally
{
connection.Close();
}
}
protected void pdfButton_Click(object sender, EventArgs e)
{
try
{
List<Course> courses = new List<Course>();
Course course = new Course();
CourseManager aCourseManager = new CourseManager();
course.ADepartment = new Department();
course.ASemester = new Semester();
course.ADepartment.DepartmentId = Convert.ToInt16(departmentDropDownList.Text);
course.ASemester.SemesterId = Convert.ToInt16(semesterDropDownList.Text);
course.CourseStatus = 0;
courses = aCourseManager.GetAllUnassignedCourses(course);
Document doc = new Document(iTextSharp.text.PageSize.LETTER, 10, 10, 42, 35);
string pdfFilePath = Server.MapPath("CoursePdf.pdf");
PdfWriter wri = PdfWriter.GetInstance(doc, new FileStream(pdfFilePath, FileMode.Create));
doc.Open(); //Open Document to write
iTextSharp.text.Font font8 = FontFactory.GetFont("ARIAL", 7);
string heading = " Unassigned Course Details for Department: " +
departmentDropDownList.SelectedItem + " and Semester: " +
semesterDropDownList.SelectedItem;
Paragraph reportHeading = new Paragraph(heading);
if (courses != null)
{
PdfPTable PdfTable = new PdfPTable(4);
PdfPCell PdfPCell = null;
PdfPCell = new PdfPCell(new Phrase(new Chunk("Course Code", font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk("Course Name", font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk("Credit", font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk("Course Description", font8)));
PdfTable.AddCell(PdfPCell);
foreach (Course aCourse in courses)
{
PdfPCell = new PdfPCell(new Phrase(new Chunk(aCourse.CourseCode, font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk(aCourse.CourseName, font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk(aCourse.Description, font8)));
PdfTable.AddCell(PdfPCell);
PdfPCell = new PdfPCell(new Phrase(new Chunk((aCourse.Credit).ToString(), font8)));
PdfTable.AddCell(PdfPCell);
}
PdfTable.SpacingBefore = 15f;
doc.Add(reportHeading);
doc.Add(PdfTable);
doc.Close();
WebClient client = new WebClient();
Byte[] buffer = client.DownloadData(pdfFilePath);
Response.ContentType = "application/pdf";
Response.AddHeader("content-length", buffer.Length.ToString());
Response.BinaryWrite(buffer);
}
}
catch (SqlException sqlException)
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = "Database error.See details error: " + sqlException.Message;
}
catch (Exception exception)
{
msgLabel.ForeColor = Color.Red;
string errorMessage = "Unknow error occured.";
errorMessage += exception.Message;
if (exception.InnerException != null)
{
errorMessage += exception.InnerException.Message;
}
msgLabel.Text = errorMessage;
}
}
private void GetAllUnassignedCourses()
{
try
{
List<Course> courses = new List<Course>();
Course aCourse = new Course();
CourseManager aCourseManager = new CourseManager();
aCourse.ADepartment = new Department();
aCourse.ASemester = new Semester();
aCourse.ADepartment.DepartmentId = Convert.ToInt16(departmentDropDownList.Text);
aCourse.ASemester.SemesterId = Convert.ToInt16(semesterDropDownList.Text);
aCourse.CourseStatus = 0;
courses = aCourseManager.GetAllUnassignedCourses(aCourse);
courseGridView.DataSource = courses;
courseGridView.DataBind();
}
catch (Exception exception)
{
throw exception;
}
}
public List<Course> GetAllUnassignedCourses(Course aCourse)
{
aCourseGateway = new CourseGateway();
return aCourseGateway.GetAllUnassignedCourses(aCourse);
}
private bool DoesThisCourseNameExist(Course aCourse)
{
List<Course> courses = new List<Course>();
courses = GetAllCourses();
foreach (Course course in courses)
{
if (course.CourseName == aCourse.CourseName)
{
return true;
}
}
return false;
}
public string SaveCourse(Course aCourse)
{
aCourseGateway = new CourseGateway();
if (!DoesThisCourseNameExist(aCourse))
if (!DoesThisCourseCodeExist(aCourse))
return aCourseGateway.SaveCourse(aCourse);
else
return "This Course Code already Exist";
else
return "This Course Name already Exist";
}
private void ShowStudentAndCourseInfo()
{
try
{
StudentManager aStudentManager = new StudentManager();
string regNo = registationNoTextBox.Text;
ViewStudentInformation aViewStudentInformation = new ViewStudentInformation();
aViewStudentInformation = aStudentManager.GetStudentInfo(regNo);
if(aViewStudentInformation.Name==null)
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = "Invalid registation Number";
return;
}
nameTextBox.Text = aViewStudentInformation.Name;
emailTextBox.Text = aViewStudentInformation.Email;
departmentTextBox.Text = aViewStudentInformation.DepartmentName;
Course aCourse = new Course();
List<Course> courses = new List<Course>();
courses = aStudentManager.GetStudentCourses(regNo);
enrollsubjectGridView.DataSource = courses;
enrollsubjectGridView.DataBind();
List<Course> courseList = new List<Course>();
courseList = aStudentManager.GetAllcourses(aViewStudentInformation);
enrollDropDownList.DataSource = courseList;
enrollDropDownList.DataBind();
}
catch (Exception exception)
{
throw exception;
}
}
public string SaveCourse(Course aCourse)
{
try
{
connection.Open();
string courseQuery = "INSERT INTO t_Course VALUES(@code,@title,@credit,@description,@semester,@deptCode,@status)";
command.CommandText = courseQuery;
command.Parameters.Clear();
command.Parameters.AddWithValue("@code", aCourse.CourseCode);
command.Parameters.AddWithValue("@title", aCourse.CourseName);
command.Parameters.AddWithValue("@credit", aCourse.Credit);
command.Parameters.AddWithValue("@description", aCourse.Description);
command.Parameters.AddWithValue("@semester", aCourse.ASemester.SemesterId);
command.Parameters.AddWithValue("@deptcode", aCourse.ADepartment.DepartmentId);
command.Parameters.AddWithValue("@status", aCourse.CourseStatus);
command.ExecuteNonQuery();
return "Saved";
}
finally
{
connection.Close();
}
}
public List<int> GetScheduleCoursesId(Course aCourse)
{
try
{
int scheduleStatus = 0;
connection.Open();
List<int> coursesId = new List<int>();
string query = "SELECT DISTINCT(courseId) FROM t_ScheduleClass WHERE DepartmentId=@departmentId AND SemesterId=@semesterId and ScheduleStatus=@status";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@departmentId", aCourse.ADepartment.DepartmentId);
command.Parameters.AddWithValue("@semesterId", aCourse.ASemester.SemesterId);
command.Parameters.AddWithValue("@status", scheduleStatus);
SqlDataReader courseIdReader = command.ExecuteReader();
while (courseIdReader.Read())
{
coursesId.Add(Convert.ToInt16(courseIdReader[0]));
}
return coursesId;
}
finally
{
connection.Close();
}
}
private void GetCourses()
{
try
{
Course aCourse = new Course();
CourseManager aCourseManager = new CourseManager();
aCourse = aCourseManager.GetCourse(Convert.ToInt16(courseTitleDropDownList.Text));
nameTextBox.Value = aCourse.CourseName;
creditTextBox.Value = aCourse.Credit.ToString();
}
catch (Exception exception)
{
throw exception;
}
}
