public StudentEnroll GetStudentByStudentId(int studentId)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "select * from StudentsRegister where Id='" + studentId + "' ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
StudentEnroll student = null;
while (reader.Read())
{
student = new StudentEnroll();
student.StudentName = reader["StudentName"].ToString();
student.StudentEmail = reader["StudentEmail"].ToString();
student.DepartmentId = (int)reader["DeptId"];
}
reader.Close();
connection.Close();
return(student);
}
public ActionResult EnrollStudent(StudentEnroll studentEnroll)
{
var studentList = _studentRegisterManager.GetAllStudents();
ViewBag.Students = new SelectList(studentList, "StudentId", "RegNumber");
var courses = _courseManager.GetAllCourses();
StudentEnroll student = _studentEnrollManager.GetStudentByStudentId(studentEnroll.StudentRegisterId);
var courseList = courses.Where(a => a.DepartmentId == student.DepartmentId);
ViewBag.Courses = new SelectList(courseList, "CourseId", "CourseName");
int rowAffected = _studentEnrollManager.EnrollStudentInCourse(studentEnroll);
if (ModelState.IsValid)
{
if (rowAffected > 0)
{
{
ViewBag.ValidationMsg = "Successfull.";
ModelState.Clear();
return(View(studentEnroll));
}
}
}
if (rowAffected == -1)
{
ViewBag.ValidationMsg = "Please select course.";
return(View(studentEnroll));
}
ViewBag.ValidationMsg = "This course is already enrolled once to this student.";
return(View(studentEnroll));
}
public List <StudentEnroll> GetStudentDropdownList()
{
List <StudentEnroll> studentList = new List <StudentEnroll>();
GenarateConnection();
using (Connection)
{
Connection.Open();
string query = "select * from Student;";
Command = new SqlCommand(query, Connection);
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
StudentEnroll student = new StudentEnroll();
student.ID = Convert.ToInt32(Reader["ID"].ToString());
student.RegistrationNo = Reader["RegistrationNo"].ToString();
studentList.Add(student);
}
}
Reader.Close();
Connection.Close();
}
return(studentList);
}
public JsonResult GetCoursesByDepartmentName(int studentId)
{
var courses = _courseManager.GetAllCourses();
StudentEnroll student = _studentEnrollManager.GetStudentByStudentId(studentId);
var courseList = courses.Where(a => a.DepartmentId == student.DepartmentId);
return(Json(courseList, JsonRequestBehavior.AllowGet));
}
public IActionResult Enroll(StudentEnroll newStudent)
{
try
{
return(CreatedAtAction("Enroll", _dbService.Enroll(newStudent)));
}
catch (Exception e)
{
return(NotFound("Exception: " + e.Message));
}
}
public int EnrollStudentInCourse(StudentEnroll studentEnroll)
{
if (studentEnroll.CourseId == 0)
{
return(-1);
}
if (IsCourseAlreadyAssigned(studentEnroll))
{
return(_studentEnrollGateway.EnrollStudentInCourse(studentEnroll));
}
return(0);
}
public ActionResult EnrollSave(StudentEnroll studentEnroll)
{
ViewBag.RegList = StudentManager.GetAllStudentRegNo();
if (ModelState.IsValid)
{
ViewBag.Message = EnrollManager.EnrollCourse(studentEnroll);
}
else
{
string message = "Validation error";
ViewBag.Message = message;
}
return(View());
}
public int EnrollStudentInCourse(StudentEnroll studentEnroll)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO EnrollStudent VALUES('" + studentEnroll.StudentRegisterId + "','" + studentEnroll.CourseId + "','" + studentEnroll.Enrolldate.ToString("yyyy-MM-dd") + "' )";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return(rowAffected);
}
public ActionResult StudentEnrollInaCourse(StudentEnroll studentEnroll)
{
//var FEmessage = "";
var model = new StudentEnrollViewModel();
if (ModelState.IsValid)
{
//var course = Mapper.Map<Course>(model);
bool isSavedSE = studentManager.Add(studentEnroll);
if (isSavedSE)
{
model.RegistrationNumberList = studentDAL.StudentGetAll().Select(c => new SelectListItem()
{
Value = c.StudentId.ToString(), Text = c.RegistrationNumber
});
model.CoursetSelectListItems = GetDefaultSelectListItem();
ViewBag.SEMessage = "Enroll Course Information Saved Successfully!";
ModelState.Clear();
return(View(model));
}
else
{
model.RegistrationNumberList = studentDAL.StudentGetAll().Select(c => new SelectListItem()
{
Value = c.StudentId.ToString(), Text = c.RegistrationNumber
});
model.CoursetSelectListItems = GetDefaultSelectListItem();
ViewBag.FMessage = "This Student Already Taken This Course";
return(View(model));
}
}
else
{
ViewBag.FMessage = "Failed";
}
//////var model = new StudentEnrollViewModel();
model.RegistrationNumberList = studentDAL.StudentGetAll().Select(c => new SelectListItem()
{
Value = c.StudentId.ToString(), Text = c.RegistrationNumber
});
model.CoursetSelectListItems = GetDefaultSelectListItem();
return(View(model));
}
public int EnrollCourse(StudentEnroll enroll)
{
string query =
"INSERT INTO StudentEnroll(StudentId,CourseId,Date) VALUES(@StudentId , @CourseId, @Date)";
SqlCommand = new SqlCommand(query, SqlConnection);
SqlCommand.Parameters.AddWithValue("@StudentId", enroll.StudentId);
SqlCommand.Parameters.AddWithValue("@CourseId", enroll.CourseId);
SqlCommand.Parameters.AddWithValue("@Date", enroll.Date);
// Command.Parameters.AddWithValue("@GradeId", enroll.GradeId);
//SqlCommand.Parameters.AddWithValue("@Action", enroll.Action);
SqlConnection.Open();
int rowAffected = SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
return(rowAffected);
}
public string EnrollCourse(StudentEnroll enroll)
{
if (!enrollGateway.IsEnrollExixts(enroll))
{
int rowAffect = enrollGateway.EnrollCourse(enroll);
if (rowAffect > 0)
{
return("Save Successful");
}
else
{
return("Save Failed");
}
}
else
{
return("A student can enroll in a course once only!!!");
}
}
public bool IsCourseAlreadyEnrolled(StudentEnroll studentEnroll)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM EnrollStudent where CourseId='" + studentEnroll.CourseId + "' and StudentId='" + studentEnroll.StudentRegisterId + "' ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
connection.Close();
return(false);
}
connection.Close();
return(true);
}
public bool IsEnrollExixts(StudentEnroll enroll)
{
string query = "SELECT * FROM StudentEnroll WHERE StudentId = @StudentId AND CourseId = @CourseId";
SqlCommand = new SqlCommand(query, SqlConnection);
SqlCommand.Parameters.AddWithValue("@StudentId", enroll.StudentId);
SqlCommand.Parameters.AddWithValue("@CourseId", enroll.CourseId);
if (SqlConnection.State != ConnectionState.Open)
{
SqlConnection.Open();
}
SqlDataReader = SqlCommand.ExecuteReader();
if (SqlDataReader.HasRows)
{
SqlDataReader.Close();
SqlConnection.Close();
return(true);
}
SqlDataReader.Close();
SqlConnection.Close();
return(false);
}
///---Student Enroll---///
public bool Add(StudentEnroll studentEnroll)
{
if (db.StudentEnrolls.Any(c => c.StudentId == studentEnroll.StudentId && c.CourseId == studentEnroll.CourseId))
{
return(false);
//db.StudentEnrolls.Add(studentEnroll);
//return db.SaveChanges() > 0;
}
else if (db.StudentEnrolls.Any(c => c.StudentId == studentEnroll.StudentId && c.CourseId != studentEnroll.CourseId))
{
db.StudentEnrolls.Add(studentEnroll);
return(db.SaveChanges() > 0);
}
else
{
db.StudentEnrolls.Add(studentEnroll);
return(db.SaveChanges() > 0);
}
//else
//{
// return false;
//}
}
///----Save Student End----///
///---- Student Enroll ----///
public bool Add(StudentEnroll studentEnroll)
{
bool isSavedSE = studentDAL.Add(studentEnroll);
return(isSavedSE);
}
private void GeneratePdf(int studentId)
{
//try
//{
Document pdfDoc = new Document(PageSize.A4, 25, 10, 25, 10);
string title = "ABC UNIVERSITY";
string subTitle = "Grade Sheet";
int columnNo = 3;
PdfPTable tableLayout = new PdfPTable(columnNo);
tableLayout.HorizontalAlignment = 1;
PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
pdfDoc.Open();
Font fTitle = new Font(Font.FontFamily.HELVETICA, 50.0f, Font.NORMAL, BaseColor.BLACK);
Font fSub = new Font(Font.FontFamily.TIMES_ROMAN, 35.0f, Font.UNDERLINE, BaseColor.BLACK);
Paragraph para = new Paragraph(title, fTitle);
para.Alignment = Element.ALIGN_CENTER;
pdfDoc.Add(para);
pdfDoc.Add(new Paragraph(" "));
Paragraph paraSub = new Paragraph(subTitle, fSub);
paraSub.Alignment = Element.ALIGN_CENTER;
pdfDoc.Add(paraSub);
pdfDoc.Add(new Paragraph(" \n\n"));
pdfDoc.Add(new Paragraph(" \n\n"));
StudentEnroll student = _studentEnrollManager.GetStudentByStudentId(studentId);
student.DepartmentName = _departmentManager.GetDepartmentNameById(student.DepartmentId);
var studentList = _studentRegisterManager.GetAllStudents();
StudentRegister aStudent = studentList.Where(a => a.StudentId == studentId).FirstOrDefault();
//string name = student.StudentName;
Font infoFont = new Font(Font.FontFamily.TIMES_ROMAN, 15.0f, Font.ITALIC, BaseColor.BLACK);
//Paragraph nameParagraph = new Paragraph("Student Name: " + name, infoFont);
pdfDoc.Add(new Paragraph("Student Name: " + student.StudentName, infoFont));
pdfDoc.Add(new Paragraph("Reg. Number: " + aStudent.RegNumber, infoFont));
pdfDoc.Add(new Paragraph("Department: " + student.DepartmentName, infoFont));
pdfDoc.Add(new Paragraph("Email: " + student.StudentEmail, infoFont));
pdfDoc.Add(new Paragraph(" \n\n"));
pdfDoc.Add(new Paragraph(" \n\n"));
//pdfDoc.Add(tableLayout);
pdfDoc.Add(Add_Content_To_PDF(tableLayout, studentId));
pdfDoc.Close();
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment;filename=GradeSheet.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Write(pdfDoc);
Response.End();
//}
//catch (Exception ex)
//{
// Response.Write(ex.Message);
//}
}
public bool IsCourseAlreadyAssigned(StudentEnroll studentEnroll)
{
return(_studentEnrollGateway.IsCourseAlreadyEnrolled(studentEnroll));
}
public Enrollment Enroll(StudentEnroll studentNew)
{
var enroll = new Enrollment();
using (SqlConnection connection = new SqlConnection(ConString))
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
comm.Transaction = transaction;
SqlDataReader reader = null;
try
{
comm.CommandText = "select IdStudy from studies where name = @name ";
comm.Parameters.AddWithValue("name", studentNew.Studies);
reader = comm.ExecuteReader();
if (!reader.Read())
{
throw new Exception("No studies: " + studentNew.Studies);
}
if (FetchStudent(studentNew.IndexNumber) != null)
{
throw new Exception("Choose different index " + studentNew.IndexNumber);
}
var idStudy = reader["IdStudy"].ToString();
reader.Close();
comm.CommandText = "select * from Enrollment where Semester = 1 and IdStudy = @idStudy ";
comm.Parameters.AddWithValue("idStudy", idStudy);
reader = comm.ExecuteReader();
string startDate;
string idEnrollment;
if (reader.Read())
{
startDate = reader["StartDate"].ToString();
idEnrollment = reader["IdEnrollment"].ToString();
}
else
{
reader.Close();
comm.CommandText = "select max(IdEnrollment) as MaxIdEnrollment from Enrollment ";
reader.Read();
idEnrollment = (int.Parse(reader["currentMax"].ToString()) + 1).ToString();
startDate = "2020-03-29";
reader.Close();
comm.CommandText = "insert into Enrollment(Semester, IdEnrollment, IdStudy, StartDate) values(@Semester, @newId, @IdStudy, @StartDate) ";
comm.Parameters.AddWithValue("Semester", 1);
comm.Parameters.AddWithValue("newId", idEnrollment);
comm.Parameters.AddWithValue("IdStudy", idStudy);
comm.Parameters.AddWithValue("StartDate", startDate);
comm.ExecuteNonQuery();
}
reader.Close();
comm.CommandText = "insert into Student (IndexNumber, FirstName, LastName, IdEnrollment, BirthDate) values(@IndexNumber, @FirstName, @LastName, @IdEnrollment, @BirthDate) ";
comm.Parameters.AddWithValue("IndexNumber", studentNew.IndexNumber);
comm.Parameters.AddWithValue("FirstName", studentNew.FirstName);
comm.Parameters.AddWithValue("LastName", studentNew.LastName);
comm.Parameters.AddWithValue("IdEnrollment", idEnrollment);
comm.Parameters.AddWithValue("BirthDate", studentNew.BirthDate);
comm.ExecuteNonQuery();
enroll = new Enrollment()
{
IdEnrollment = idEnrollment,
Semester = "1",
StartDate = startDate,
IdStudy = idStudy
};
transaction.Commit();
}
catch (Exception e)
{
if (reader != null)
{
reader.Close();
transaction.Rollback();
}
throw e;
}
}
return(enroll);
}
