public int Insert(CourseAssignToTeacher courseAssign)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "INSERT INTO t_CourseAssignToTeacher VALUES(@deptId,@teacherId,@courseId,@status)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@deptId", courseAssign.DepartmentId);
CommandObj.Parameters.AddWithValue("@teacherId", courseAssign.TeacherId);
CommandObj.Parameters.AddWithValue("@courseId", courseAssign.CourseId);
CommandObj.Parameters.AddWithValue("@status", 1);
int rowAffected = CommandObj.ExecuteNonQuery();
int updateResult = UpdateTeacher(courseAssign);
sqlTransaction.Commit();
return(rowAffected);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public int UpdateTeacherRemainCredit(CourseAssignToTeacher courseAssignToTeacher)
{
int teacherId = courseAssignToTeacher.TeacherId;
double courseCredit = courseAssignToTeacher.CourseCredit;
return(courseAssignToTeacherGateway.UpdateTeacherRemainCredit(teacherId, courseCredit));
}
public int Insert(CourseAssignToTeacher courseAssignToTeacher)
{
Query = "INSERT INTO CourseAssignToTeacher(DepartmentId,TeacherId,CourseId,IsActive)" +
" VALUES(@DepartmentId,@TeacherId,@CourseId,@IsActive)";
SqlCommand Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("DepartmentId", SqlDbType.Int);
Command.Parameters["DepartmentId"].Value = courseAssignToTeacher.DepartmentId;
Command.Parameters.Add("TeacherId", SqlDbType.Int);
Command.Parameters["TeacherId"].Value = courseAssignToTeacher.TeacherId;
Command.Parameters.Add("CourseId", SqlDbType.Int);
Command.Parameters["CourseId"].Value = courseAssignToTeacher.CourseId;
Command.Parameters.Add("IsActive", SqlDbType.Bit);
Command.Parameters["IsActive"].Value = 1;
Connection.Open();
Command.ExecuteNonQuery();
int updateResult = UpdateTeacher(courseAssignToTeacher);
Connection.Close();
return(updateResult);
}
public List <CourseAssignToTeacher> GetAllCourseAssignToTeachers(int deptId)
{
string query = "SELECT * FROM CourseAssignToTeacherView WHERE deptId = " + deptId + "";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <CourseAssignToTeacher> courses = new List <CourseAssignToTeacher>();
while (reader.Read())
{
CourseAssignToTeacher aCourse = new CourseAssignToTeacher();
aCourse.CourseCode = reader["Code"].ToString();
aCourse.CourseName = reader["CourseName"].ToString();
aCourse.Semester = reader["Semester"].ToString();
aCourse.TeacherName = reader["TeacherName"].ToString();
courses.Add(aCourse);
}
connection.Close();
return(courses);
}
//Status False
public List <CourseAssignToTeacher> GetAllCourseassinAssignToTeachersFalse()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM t_courseassingtoteacher WHERE Status='0'";
SqlCommand command = new SqlCommand(query, connection);
List <CourseAssignToTeacher> aList = new List <CourseAssignToTeacher>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseAssignToTeacher assignToTeacher = new CourseAssignToTeacher();
assignToTeacher.TeacherId = Convert.ToInt32(reader["TeacherId"].ToString());
assignToTeacher.CourseId = Convert.ToInt32(reader["CourseId"].ToString());
assignToTeacher.Status = Convert.ToBoolean(reader["Status"].ToString());
aList.Add(assignToTeacher);
}
reader.Close();
}
connection.Close();
return(aList);
}
public int Update(CourseAssignToTeacher courseAssign)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UPDATE t_CourseAssignToTeacher SET IsActive=1 WHERE TeacherId='" + courseAssign.TeacherId + "' AND CourseId='" + courseAssign.CourseId + "'";
CommandObj.ExecuteNonQuery();
int updateResult = UpdateTeacher(courseAssign);
sqlTransaction.Commit();
return(updateResult);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
//public string Save(CourseAssignToTeacher courseAssignToTeacher)
//{
// return courseAssignToTeacherGateway.Save(courseAssignToTeacher);
//}
public string Save(CourseAssignToTeacher courseAssignToTeacher)
{
CourseAssignToTeacher courseAssignTo = GetAll.ToList().Find(ca => ca.CourseId == courseAssignToTeacher.CourseId);
if (courseAssignTo == null)
{
if (courseAssignToTeacherGateway.Insert(courseAssignToTeacher) > 0)
{
return("Saved sucessfully");
}
return("Failed to save");
}
CourseAssignToTeacher assignTo = GetAll.ToList().Find(c => c.CourseId == courseAssignToTeacher.CourseId && c.TeacherId == courseAssignToTeacher.TeacherId);
if (assignTo != null)
{
bool st = assignTo.Status;
if (st)
{
return("Overlapping occured(Course has been assigned to this Teacher Already)");
}
if (courseAssignToTeacherGateway.Update(courseAssignToTeacher) > 0)
{
return("Saved sucessfully");
}
return("Failed to save");
}
return("Overlapping Occured!(Course has already been Assigned to Another Teacher )");
}
public string Save(CourseAssignToTeacher courseAssign)
{
CourseAssignToTeacher courseAssignTo = GetAll.ToList().Find(ca => ca.CourseId == courseAssign.CourseId && ca.Status);
if (courseAssignTo == null)
{
if (courseAssignToTeacherGateway.Insert(courseAssign) > 0)
{
return("Saved sucessfully");
}
return("Failed to save");
}
//CourseAssignToTeacher assignTo =
// GetAll.ToList().Find(c => c.CourseId == courseAssign.CourseId && c.TeacherId == courseAssign.TeacherId);
//if (assignTo != null)
//{
// bool st = assignTo.Status;
// if (st)
// {
// return "Overlaping not allowed during course assign";
// }
// if (courseAssignToTeacherGateway.Update(courseAssign) > 0)
// {
// return "Saved sucessfully";
// }
// return "Failed to save";
//}
return("Overlaping not allowed!");
}
public List <CourseAssignToTeacher> GetAllCourseAssignToTeacher()
{
CommandObj.CommandText = "SELECT * FROM CourseAssignToTeacher_tbl";
List <CourseAssignToTeacher> courseAssignToTeachers = new List <CourseAssignToTeacher>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
CourseAssignToTeacher assignToTeacher = new CourseAssignToTeacher
{
Id = Convert.ToInt32(reader["Id"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
TeacherId = Convert.ToInt32(reader["TeacherId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
Status = Convert.ToBoolean(reader["IsAssign"].ToString())
};
courseAssignToTeachers.Add(assignToTeacher);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(courseAssignToTeachers);
}
public int UnallocationCourseAssignToTeacher(CourseAssignToTeacher courseAssignToTeacher)
{
//courseAssign.Status = 1;
string query = "Update CourseAssigntoTeacher Set Status = 0";
SqlCommand command = new SqlCommand(query, Connection);
Connection.Open();
int rowAffected = command.ExecuteNonQuery();
Connection.Close();
string query2 = "Update Teacher Set RemainingCredit = CreditTobeTaken";
SqlCommand command2 = new SqlCommand(query2, Connection);
Connection.Open();
command2.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public IEnumerable <CourseAssignToTeacher> GetAssignToTeachers()
{
connection = new SqlConnection(connectionString);
string query = "SELECT * FROM CourseAssignToTeacher";
command = new SqlCommand(query, connection);
List <CourseAssignToTeacher> courses = new List <CourseAssignToTeacher>();
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
CourseAssignToTeacher course = new CourseAssignToTeacher();
{
course.Id = Convert.ToInt32(reader["Id"].ToString());
course.DepartmentId = (int)reader["DepartmentId"];
course.TeacherId = (int)reader["TeacherId"];
course.CourseId = (int)reader["CourseId"];
course.Status = (bool)reader["IsActive"];
}
courses.Add(course);
}
reader.Close();
return(courses);
}
public ActionResult DeleteConfirmed(int id)
{
CourseAssignToTeacher courseassign = db.CourseAssignToTeachers.Find(id);
db.CourseAssignToTeachers.Remove(courseassign);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult CourseAssignToTeacher(CourseAssignToTeacher courseAssignToTeacher)
{
ViewBag.Message = courseAssignToTeacherManager.Save(courseAssignToTeacher);
ViewBag.Departments = departmentManager.GetAll();
ViewBag.Teachers = teacherManager.GetAllTeacher();
ViewBag.Courses = courseManager.GetAll();
return(View());
}
public ActionResult AssignToTeacher(CourseAssignToTeacher assign)
{
Teacher aTeacher = new Teacher();
DepartmentManager aDepartmentManager = new DepartmentManager();
CourseAssignToTeacherManager assignToTeacherManager = new CourseAssignToTeacherManager();
TeacherManager aTeacherManager = new TeacherManager();
ViewBag.departments = aDepartmentManager.GetAllDepartmentInfo();
aTeacherManager.GetAllTeachers();
if (aTeacher.RemainingCredit < assign.CourseCredit)
{
}
var assinglist = assignToTeacherManager.GetAllCourseassinAssignToTeachers();
var assingcoursttoteacher = assinglist.FirstOrDefault(t => t.CourseId == assign.CourseId);
var assigntrue = assignToTeacherManager.GetAllCourseassinAssignToTeachersTrue();
var assigntruefind = assigntrue.FirstOrDefault(c => c.CourseId == assign.CourseId);
var assignfalse = assignToTeacherManager.GetAllCourseassinAssignToTeachersFalse();
var assigntfalsefind = assignfalse.FirstOrDefault(c => c.CourseId == assign.CourseId);
if (assign.CourseCredit == 0.0)
{
ViewBag.message = "Input Values";
}
else
{
if (assigntruefind != null)
{
ViewBag.message = "Course Already Assigned";
}
else if (assigntfalsefind != null)
{
if (assignToTeacherManager.UpdateAssignCourse(assign) > 0)
{
ViewBag.message = "Assign Successfull";
}
else
{
ViewBag.message = "Assign Failed";
}
}
else
{
if (assignToTeacherManager.AssignCourseToTeacher(assign) > 0)
{
assignToTeacherManager.UpdateRemainingCredit(assign.CourseCredit, assign.TeacherId);
ViewBag.message = "Assign Successfull";
}
else
{
ViewBag.message = "Failed to Assign";
}
}
}
return(View());
}
public ActionResult CourseAssignToTeacher(CourseAssignToTeacher courseAssign)
{
List <Department> departments = departmentManager.GetAllDepartments();
ViewBag.Departments = departments;
string msg = courseAssignToTeacher.Save(courseAssign);
ViewBag.Messages = msg;
return(View());
}
private int UpdateTeacher(CourseAssignToTeacher courseAssign)
{
Teacher teacher = teacherManager.GetAll.ToList().Find(t => t.Id == courseAssign.TeacherId);
double creditTakenbyTeacher = Convert.ToDouble(teacher.CreditTaken) + Convert.ToDouble(courseAssign.Credit);
CommandObj.CommandText = "Update t_Teacher Set CreditTaken='" + creditTakenbyTeacher + "' WHERE Id='" +
courseAssign.TeacherId + "'";
return(CommandObj.ExecuteNonQuery());
}
public int Update(CourseAssignToTeacher courseAssignToTeacher)
{
Query = "UPDATE CourseAssignToTeacher SET IsActive=1 WHERE TeacherId='" + courseAssignToTeacher.TeacherId + "' AND CourseId='" + courseAssignToTeacher.CourseId + "'";
SqlCommand Command = new SqlCommand(Query, Connection);
Connection.Open();
int rowsAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowsAffected);
}
public bool IsSubjectAssigned(CourseAssignToTeacher courseAssign)
{
string query = "SELECT * FROM CourseAssignToTeacher WHERE CourseCode='" + courseAssign.CourseCode + "' AND TeacherId='" + courseAssign.TeacherId + "'";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
bool IsExsists = Reader.HasRows;
Connection.Close();
return(IsExsists);
}
//Update
public int UpdateAssignCourse(CourseAssignToTeacher assign)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "Update t_courseassingtoteacher Set TeacherId = '" + assign.TeacherId + "',Status = '1' WHERE (CourseId = '" + assign.CourseId + "')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
return(rowsAffected);
}
public int AssignCourseToTeacher(CourseAssignToTeacher assign)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO t_courseassingtoteacher (TeacherId,CourseId,Status) VALUES('" + assign.TeacherId + "','" + assign.CourseId + "','1')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
return(rowsAffected);
}
public int Save(CourseAssignToTeacher courseAssign)
{
string query = "INSERT INTO CourseAssignToTeacher (DepartmentId,TeacherId,CourseCodeId) VALUES (" + courseAssign.DepartmentId + "," + courseAssign.TeacherId + "," + courseAssign.CourseCodeId + ")";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rawAffected = command.ExecuteNonQuery();
connection.Close();
return(rawAffected);
}
private int UpdateTeacher(CourseAssignToTeacher courseAssignToTeacher)
{
Teacher teacher = teacherManager.GetAllTeacher().ToList().Find(t => t.Id == courseAssignToTeacher.TeacherId);
double creditTakenbyTeacher = Convert.ToDouble(teacher.CreditTaken) + Convert.ToDouble(courseAssignToTeacher.Credit);
Query = "Update Teachers Set CreditTaken='" + creditTakenbyTeacher + "' WHERE Id='" +
courseAssignToTeacher.TeacherId + "'";
SqlCommand Command = new SqlCommand(Query, Connection);
int i = Command.ExecuteNonQuery();
Connection.Close();
return(i);
}
private int UpdateTeacher(CourseAssignToTeacher courseAssign)
{
Teacher teacher = teacherManager.GetAllTeachers().ToList().Find(t => t.Id == courseAssign.TeacherId);
decimal creditTakenbyTeacher = Convert.ToDecimal(teacher.CreditTaken) + Convert.ToDecimal(courseAssign.Credit);
CommandObj.CommandText = "Update Teacher_tbl Set CreditTaken=@creditTaken WHERE Id=@id";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("creditTaken", creditTakenbyTeacher);
CommandObj.Parameters.AddWithValue("id", courseAssign.TeacherId);
return(CommandObj.ExecuteNonQuery());
}
public ActionResult courseAsssignation(CourseAssignToTeacher c)
{
int schoolid = Convert.ToInt32(Session["school"]);
ClassDBHandle gc = new ClassDBHandle();
List <tbl_ClassValidation> list = gc.GetClass(schoolid);
ViewData["class_name"] = new SelectList(list, "Class_Id", "Name");
return(View());
}
public ActionResult Edit([Bind(Include = "Id,DepartmentId,TeacherId,CreditTaken,CreditLeft,CourseID,Name,Credit")] CourseAssignToTeacher courseassign)
{
if (ModelState.IsValid)
{
db.Entry(courseassign).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CourseID = new SelectList(db.Courses, "Id", "CourseCode", courseassign.CourseID);
ViewBag.DepartmentId = new SelectList(db.Deparments, "Id", "DeptCode", courseassign.DepartmentId);
ViewBag.TeacherId = new SelectList(db.Teachers, "Id", "Name", courseassign.TeacherId);
return(View(courseassign));
}
public string SaveCourseAssignToTeacher(CourseAssignToTeacher courseAssignToTeacher)
{
int rowEffected = courseAssignToTeacherGetway.SaveCourseAssignToTeacher(courseAssignToTeacher);
if (rowEffected > 0)
{
return("Successfully Save");
}
else
{
return("Failed Save");
}
}
// GET: /CourseAssign/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
CourseAssignToTeacher courseassign = db.CourseAssignToTeachers.Find(id);
if (courseassign == null)
{
return(HttpNotFound());
}
return(View(courseassign));
}
public ActionResult UnallocationCourseAssignToTeacher(CourseAssignToTeacher courseAssignToTeacher)
{
int rowsAffected = courseAssignToTeacherManager.UnallocationCourseAssignToTeacher(courseAssignToTeacher);
if (rowsAffected > 0)
{
ViewBag.Message = "Unassign Teacher Successfully";
}
else
{
ViewBag.Message = "Unassign failed";
}
return(View());
}
public bool IsExistCourse(CourseAssignToTeacher courseAssignToTeacher)
{
string query = "SELECT * FROM CourseAssignToTeacher WHERE CourseId = " + courseAssignToTeacher.CourseId + "";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
bool isEixst = reader.HasRows;
connection.Close();
return(isEixst);
}
public JsonResult SaveCourseAssign(CourseAssignToTeacher courseAssign)
{
var checkAssignedCoueses =
db.CourseAssignToTeachers.Where(m => m.CourseID == courseAssign.CourseID)
.ToList();
if (checkAssignedCoueses.Count > 0)
{
return(Json(false));
}
else
{
db.CourseAssignToTeachers.Add(courseAssign);
db.SaveChanges();
var teacher = db.Teachers.FirstOrDefault(m => m.Id == courseAssign.TeacherId);
if (teacher != null)
{
//teacher.CreditLeft = CourseAssignToTeacher.CreditLeft;
teacher.CreditLeft = courseAssign.CreditLeft;
db.Teachers.AddOrUpdate(teacher);
db.SaveChanges();
var course = db.Courses.FirstOrDefault(m => m.Id == courseAssign.CourseID);
if (course != null)
{
//course.CourseStatus = true;
course.CourseAssignTo = teacher.Name;
courseAssign.Name = course.Course_Name;
courseAssign.Credit = course.Course_Credit;
db.Courses.AddOrUpdate(course);
db.SaveChanges();
return(Json(true));
}
else
{
return(Json(false));
}
}
else
{
return(Json(false));
}
}
}